pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfunctions are returns columns
7+ messages / 4 participants
[nested] [flat]
* functions are returns columns
@ 2007-11-09 19:46 Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>
2007-11-09 20:35 ` Re: functions are returns columns Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Michele Petrazzo - Unipex srl @ 2007-11-09 19:46 UTC (permalink / raw)
To: pgsql-sql
Hi all.
I want that a function return a table rows (like the doc says at 33.4.4.
SQL Functions as Table Sources), but I want the a function return only a
few cols, so the same that I select into the func.
Modifying the doc example:
CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe');
INSERT INTO foo VALUES (1, 2, 'Ed');
INSERT INTO foo VALUES (2, 1, 'Mary');
CREATE FUNCTION getfoo(int) RETURNS foo AS $$
SELECT fooid, foosubid FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
This give me an error:
ERROR: return type mismatch in function declared to return foo
DETAIL: Final SELECT returns too few columns.
CONTEXT: SQL function "getfoo"
So, how do it?
Thanks,
Michele
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: functions are returns columns
2007-11-09 19:46 functions are returns columns Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>
@ 2007-11-09 20:35 ` Pavel Stehule <pavel.stehule@gmail.com>
2007-11-10 17:37 ` Re: functions are returns columns Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>
0 siblings, 1 reply; 7+ messages in thread
From: Pavel Stehule @ 2007-11-09 20:35 UTC (permalink / raw)
To: Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>; +Cc: pgsql-sql
On 09/11/2007, Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it> wrote:
> Hi all.
> I want that a function return a table rows (like the doc says at 33.4.4.
> SQL Functions as Table Sources), but I want the a function return only a
> few cols, so the same that I select into the func.
> Modifying the doc example:
>
> CREATE TABLE foo (fooid int, foosubid int, fooname text);
> INSERT INTO foo VALUES (1, 1, 'Joe');
> INSERT INTO foo VALUES (1, 2, 'Ed');
> INSERT INTO foo VALUES (2, 1, 'Mary');
>
> CREATE FUNCTION getfoo(int) RETURNS foo AS $$
> SELECT fooid, foosubid FROM foo WHERE fooid = $1;
> $$ LANGUAGE SQL;
>
> This give me an error:
>
> ERROR: return type mismatch in function declared to return foo
> DETAIL: Final SELECT returns too few columns.
> CONTEXT: SQL function "getfoo"
>
>
CREATE FUNCTION getfoo(int) RETURNS foo AS $$
SELECT fooid, foosubid FROM foo WHERE fooid = $1 LIMIT 1;
$$ LANGUAGE SQL;
or
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
SELECT fooid, foosubid FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
try:
SELECT * FROM getfoo(1);
Regards
Pavel Stehule
> So, how do it?
>
> Thanks,
> Michele
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: functions are returns columns
2007-11-09 19:46 functions are returns columns Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>
2007-11-09 20:35 ` Re: functions are returns columns Pavel Stehule <pavel.stehule@gmail.com>
@ 2007-11-10 17:37 ` Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>
2007-11-10 19:36 ` Re: functions are returns columns Gregory Stark <stark@enterprisedb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Michele Petrazzo - Unipex srl @ 2007-11-10 17:37 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: functions are returns columns
2007-11-09 19:46 functions are returns columns Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>
2007-11-09 20:35 ` Re: functions are returns columns Pavel Stehule <pavel.stehule@gmail.com>
2007-11-10 17:37 ` Re: functions are returns columns Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>
@ 2007-11-10 19:36 ` Gregory Stark <stark@enterprisedb.com>
2007-11-10 19:46 ` Re: functions are returns columns Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Gregory Stark @ 2007-11-10 19:36 UTC (permalink / raw)
To: Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>; +Cc: pgsql-sql
"Michele Petrazzo - Unipex srl" <michele.petrazzo@unipex.it> 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!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: functions are returns columns
2007-11-09 19:46 functions are returns columns Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>
2007-11-09 20:35 ` Re: functions are returns columns Pavel Stehule <pavel.stehule@gmail.com>
2007-11-10 17:37 ` Re: functions are returns columns Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>
2007-11-10 19:36 ` Re: functions are returns columns Gregory Stark <stark@enterprisedb.com>
@ 2007-11-10 19:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-10 21:04 ` Re: functions are returns columns Gregory Stark <stark@enterprisedb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-11-10 19:46 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>; pgsql-sql
Gregory Stark <stark@enterprisedb.com> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: functions are returns columns
2007-11-09 19:46 functions are returns columns Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>
2007-11-09 20:35 ` Re: functions are returns columns Pavel Stehule <pavel.stehule@gmail.com>
2007-11-10 17:37 ` Re: functions are returns columns Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>
2007-11-10 19:36 ` Re: functions are returns columns Gregory Stark <stark@enterprisedb.com>
2007-11-10 19:46 ` Re: functions are returns columns Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-11-10 21:04 ` Gregory Stark <stark@enterprisedb.com>
2007-11-10 23:14 ` Re: functions are returns columns Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Gregory Stark @ 2007-11-10 21:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>; pgsql-sql
"Tom Lane" <tgl@sss.pgh.pa.us> writes:
> Gregory Stark <stark@enterprisedb.com> 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!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: functions are returns columns
2007-11-09 19:46 functions are returns columns Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>
2007-11-09 20:35 ` Re: functions are returns columns Pavel Stehule <pavel.stehule@gmail.com>
2007-11-10 17:37 ` Re: functions are returns columns Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>
2007-11-10 19:36 ` Re: functions are returns columns Gregory Stark <stark@enterprisedb.com>
2007-11-10 19:46 ` Re: functions are returns columns Tom Lane <tgl@sss.pgh.pa.us>
2007-11-10 21:04 ` Re: functions are returns columns Gregory Stark <stark@enterprisedb.com>
@ 2007-11-10 23:14 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2007-11-10 23:14 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>; pgsql-sql; pgsql-hackers@postgresql.org
Gregory Stark <stark@enterprisedb.com> writes:
> "Tom Lane" <tgl@sss.pgh.pa.us> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-11-10 23:14 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-09 19:46 functions are returns columns Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>
2007-11-09 20:35 ` Pavel Stehule <pavel.stehule@gmail.com>
2007-11-10 17:37 ` Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it>
2007-11-10 19:36 ` Gregory Stark <stark@enterprisedb.com>
2007-11-10 19:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-10 21:04 ` Gregory Stark <stark@enterprisedb.com>
2007-11-10 23:14 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox