Re: [SQL] what exactly is a query structure?
On 02/26/10 10:19, A. Kretschmer wrote: In response to silly sad : hello. Postgresql 8.3.9 CREATE TYPE usr_secrets AS (login TEXT, pass TEXT, shop_pass TEXT); CREATE OR REPLACE FUNCTION get_noobs () RETURNS SETOF usr_secrets AS $$ BEGIN RETURN QUERY SELECT login, '*' as pass, shop_pass FROM noob; RETURN; END; $$ LANGUAGE plpgsql SECURITY DEFINER; SELECT * from get_noobs(); And we have the following error ERROR: structure of query does not match function result type Wild guess: your table noob has an other structure as expected, in particular login and/or shop_pass are not TEXT. they are texts. if we substitute constant '*' with a text field or even a subselect, the error disappear. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] what exactly is a query structure?
On 02/26/10 09:50, silly sad wrote: hello. Postgresql 8.3.9 CREATE TYPE usr_secrets AS (login TEXT, pass TEXT, shop_pass TEXT); CREATE OR REPLACE FUNCTION get_noobs () RETURNS SETOF usr_secrets AS $$ BEGIN RETURN QUERY SELECT login, '*' as pass, shop_pass FROM noob; RETURN; END; $$ LANGUAGE plpgsql SECURITY DEFINER; SELECT * from get_noobs(); And we have the following error ERROR: structure of query does not match function result type my own wild guess: string constant '*' is of type "unknown" -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] what exactly is a query structure?
In response to silly sad : > > my own wild guess: > string constant '*' is of type "unknown" Maybe. Add a explicit cast, for instance '*'::text Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] what exactly is a query structure?
"A. Kretschmer" writes: > In response to silly sad : >> my own wild guess: >> string constant '*' is of type "unknown" > Maybe. Add a explicit cast, for instance '*'::text Definitely. More recent versions of PG provide a more explicit error message: regression=# SELECT * from get_noobs(); ERROR: structure of query does not match function result type DETAIL: Returned type unknown does not match expected type text in column "pass". CONTEXT: PL/pgSQL function "get_noobs" line 2 at RETURN QUERY regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Date comparison, user defined operators and magic
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hello everybody, I was missing a comparison operator for DATE so I
wrote one after a really fast look into the documentation. Working
with version 8.4.
create or replace function vav_date_posterior(date, date) RETURNS
boolean AS $$
- -- return TRUE if $1 < $2, FALSE otherway
DECLARE
d2 ALIAS FOR $1;
d1 ALIAS FOR $2;
result BOOLEAN;
delta1 interval;
delta2 interval;
ini_date date;
BEGIN
ini_date := cast ('101-01-01' as date);
delta1 := d1 - ini_date;
delta2 := d2 - ini_date;
result := false;
if (delta1 > delta2) then
result := true;
end if;
return result;
END;
$$
LANGUAGE plpgsql;
CREATE OPERATOR < (
leftarg = date,
rightarg = date,
procedure = vav_date_posterior,
commutator = <
);
Then I tested it:
select vav_date_posterior(date '2001-01-2', date '2001-03-20'),
vav_date_posterior(date '2002-01-3', date '2001-03-20'),
vav_date_posterior(date '2001-01-4', date '2001-01-4'),
date '2001-01-5' <> date '2001-01-5',
date '2001-01-5' > date '2001-01-5',
date '2001-01-5' < date '2001-01-5',
date '2001-01-5' = date '2001-01-5',
date '2001-01-6' > date '2001-01-5',
date '2001-01-6' < date '2001-01-5',
date '2001-01-5' >= date '2001-01-5',
date '2001-01-5' <= date '2001-01-5',
date '2001-01-6' >= date '2001-01-5',
date '2001-01-5' <= date '2001-01-15';
And EVERYTHING was working!
So I started to wonder how is this possible because after doing the
first comparison using the < operator I really wasn't expecting any of
the other operators to work at all. But they did!
I thought ok, the > operator was inferred as it's the inverse function
for the operator I have just defined. As this one was magically
inferred, probably the equal operator was also automagically created
as the exclusion of the other two, so if A > B is FALSE, and B > A is
FALSE, we can assume that A = B. As the for the <>, >=, <=, the logic
from this point on would be quite straight forward.
The problem is that I then went back to the documentation and I red
the next page:
http://www.postgresql.org/docs/8.4/interactive/xoper-optimization.html
After reading that I understand that I'd actually have to go remove
the COMMUTATOR keyword from there as the function is not commutative
one, add a NEGATOR, define the > operator and do the same, and then go
for the = operator and so on.
But the thing is it's working...
So question is:
Is it this normal behavior?
Could someone please give a working example or a pointer to an
implemented comparison function?
Could someone please point me to the fastest way to do DATE comparison?
What would it be the fastest way of correctly implementing comparison
operators for the DATE type?
Thank you very much in advance,
Petru Ghita
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkuIjK8ACgkQt6IL6XzynQSDRQCfVLY8XEUZXUyMFWTiCzbG6PqE
828An3v47bGjM9p4oXltivmZZ+UFe6kr
=761N
-END PGP SIGNATURE-
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Date comparison, user defined operators and magic
Petru Ghita writes: > Hello everybody, I was missing a comparison operator for DATE so I > wrote one after a really fast look into the documentation. Huh? regression=# \do < List of operators Schema | Name |Left arg type| Right arg type | Result type | Description +--+-+-+-+- ... pg_catalog | <| date| date | boolean | less-than ... > So I started to wonder how is this possible because after doing the > first comparison using the < operator I really wasn't expecting any of > the other operators to work at all. But they did! They were all there before. I doubt it was using yours even in the < case, because pg_catalog is normally at the front of the search path. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
