Re: [SQL] functions are returns columns
Pavel Stehule wrote: CREATE FUNCTION getfoo(int) RETURNS foo AS $$ SELECT fooid, foosubid FROM foo WHERE fooid = $1 LIMIT 1; $$ LANGUAGE SQL; this return only one value, I need all the values that return the query or CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$ SELECT fooid, foosubid FROM foo WHERE fooid = $1; $$ LANGUAGE SQL; this create the same error: ERROR: return type mismatch in function declared to return foo DETAIL: Final SELECT returns too few columns. CONTEXT: SQL function "getfoo I try with: CREATE FUNCTION getfoo (IN int, OUT int, OUT int) AS $$ SELECT fooid, foosubid FROM foo WHERE fooid = $1; $$ LANGUAGE SQL; but only one row returned... Looking for other tips! Thanks, Michele ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] functions are returns columns
"Michele Petrazzo - Unipex srl" <[EMAIL PROTECTED]> writes: > I try with: > CREATE FUNCTION getfoo (IN int, OUT int, OUT int) AS $$ >SELECT fooid, foosubid FROM foo WHERE fooid = $1; > $$ LANGUAGE SQL; > > but only one row returned... You're almost there: CREATE FUNCTION getfoo (IN int, OUT int, OUT int) returns setof record(int,int) AS $$ SELECT fooid, foosubid FROM foo WHERE fooid = $1; $$ LANGUAGE SQL; The return type if present has to match the OUT (and BOTH) parameters. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] functions are returns columns
Gregory Stark <[EMAIL PROTECTED]> writes: > You're almost there: > CREATE FUNCTION getfoo (IN int, OUT int, OUT int) returns setof > record(int,int) AS $$ > SELECT fooid, foosubid FROM foo WHERE fooid = $1; > $$ LANGUAGE SQL; Not quite --- it's just "returns setof record". The output column types are defined by the OUT parameters. The only reason you need the returns clause is to have a place to stick the "setof" specification ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] functions are returns columns
"Tom Lane" <[EMAIL PROTECTED]> writes: > Gregory Stark <[EMAIL PROTECTED]> writes: >> You're almost there: > >> CREATE FUNCTION getfoo (IN int, OUT int, OUT int) returns setof >> record(int,int) AS $$ >> SELECT fooid, foosubid FROM foo WHERE fooid = $1; >> $$ LANGUAGE SQL; > > Not quite --- it's just "returns setof record". The output column types > are defined by the OUT parameters. The only reason you need the returns > clause is to have a place to stick the "setof" specification ... ok... I did test my example before posting it: postgres=# postgres=# CREATE or replace FUNCTION getfoo (IN int, OUT int, OUT int) returns setof record(int,int)AS $$ SELECT 1,2 union all select 2,3; $$ LANGUAGE SQL; postgres$# postgres$# CREATE FUNCTION postgres=# postgres=# select * from getfoo(1); column1 | column2 -+- 1 | 2 2 | 3 (2 rows) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] functions are returns columns
Gregory Stark <[EMAIL PROTECTED]> writes: > "Tom Lane" <[EMAIL PROTECTED]> writes: >> Not quite --- it's just "returns setof record". > I did test my example before posting it: > postgres=# postgres=# CREATE or replace FUNCTION getfoo (IN int, OUT int, OUT > int) returns setof record(int,int)AS $$ >SELECT 1,2 union all select 2,3; > $$ LANGUAGE SQL; Interesting --- if you try it in anything older than 8.3, it will fail. What is happening here is that the "(int,int)" is being taken as a typmod (per Teodor's work to allow typmods for all data types), and apparently in this path we never check to see if it's a *valid* typmod. Now typmods are always discarded from function argument and result types, but it seems like we'd better validate that they're legal for the datatype anyway. Otherwise there will be confusion of just this sort. Comments, objections? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] general question on optimizer
I have found that in many complex queries left join is exponentially faster than a (not) exists clause. I don't understand why, generally speaking, this is so frequently so effective. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
