Thread: A question about "table function"

hi, all:

   When I see the following definitions from postgresql document. I am
completely puzzeled.

CREATE TABLE foo (fooid int, foosubid int, fooname text);

>> As I say it, foo is already a set of columns.

CREATE FUNCTION getfoo(int) RETURNS setof foo AS '
    SELECT * FROM foo WHERE fooid = $1;
' LANGUAGE SQL;

>> here return type should be "setof setof foo".

----------------------------------------------------------------------------
-----------------
Can I rewrite the definition as follows:

CREATE TYPE foo (fooid int, foosubid int, fooname text);

CREATE FUNCTION getfoo(int) RETURNS setof foo AS '
    SELECT * FROM foo WHERE fooid = $1;
' LANGUAGE SQL;

----------------------------------------------------------------------------
-------------------

any comments are appriciate.

JACK DULL

Yuan Lin wrote:
> CREATE TABLE foo (fooid int, foosubid int, fooname text);
>>>As I say it, foo is already a set of columns.

"setof" refers to returning multiple rows. Multiple columns is referred
to as "composite type".

> CREATE FUNCTION getfoo(int) RETURNS setof foo AS '
>     SELECT * FROM foo WHERE fooid = $1;
> ' LANGUAGE SQL;
>
>>>here return type should be "setof setof foo".

No, the documentation is correct.

> Can I rewrite the definition as follows:
>
> CREATE TYPE foo (fooid int, foosubid int, fooname text);
>
> CREATE FUNCTION getfoo(int) RETURNS setof foo AS '
>     SELECT * FROM foo WHERE fooid = $1;
> ' LANGUAGE SQL;

You can do this *if* you don't have a table by the same name. If you do,
the CREATE TYPE will fail (but you can always try it if you need to
prove it to yourself).

Joe