Re: functions are returns columns

On 09/11/2007, Michele Petrazzo - Unipex srl <michele(dot)petrazzo(at)unipex(dot)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
>