On 09/11/2007, Michele Petrazzo - Unipex srl <[EMAIL PROTECTED]> 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
>
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
