Обсуждение: Re: SETOF (was: Function example returning more then 1

>       You can't return more than one return value from function, but that
> value can be of composite type.For example :"setof text" or "setof record" > are such types.

        Can you use functions returning "setof"s as if they were "normal" selects?.

        I saw that setof takes a type or table as an argument. What if what i want to return is not in a table schema, can i do something like "setof (blah inet, blah2 varchar(256))"?

On Thu, 27 Feb 2003, Kolus Maximiliano wrote:

> > You can't return more than one return value from function, but that
> > value can be of composite type.For example :"setof text" or "setof record"
> > are such types.
>
> Can you use functions returning "setof"s as if they were "normal"
> selects?.
>
> I saw that setof takes a type or table as an argument. What if what
> i want to return is not in a table schema, can i do something like "setof
> (blah inet, blah2 varchar(256))"?



CREATE TYPE compfoo AS (f1 int, f2 text);

CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS 'SELECT fooid, fooname
FROM foo' LANGUAGE SQL;

or use setof record if column types aren't fixed. But then you need to use
syntax like

SELECT *
 FROM dblink('dbname=template1', 'select proname, prosrc from pg_proc')
   AS t1(proname name, prosrc text)
 WHERE proname LIKE 'bytea%';

More info about table functions is available in PostgreSQL 7.3
Programmer's Guide II. Server Programming in chapter "9.7 Table Funtions".

http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=xfunc-tablefunctions.html

--
Antti Haapala

On Thu, 27 Feb 2003, Kolus Maximiliano wrote:

> >     You can't return more than one return value from function, but that
> > value can be of composite type.For example :"setof text" or "setof record"
> > are such types.
>
>     Can you use functions returning "setof"s as if they were "normal"
> selects?.
>
>     I saw that setof takes a type or table as an argument. What if what
> i want to return is not in a table schema, can i do something like "setof
> (blah inet, blah2 varchar(256))"?

You might want to read the Set Returning Functions document on techdocs:
http://techdocs.postgresql.org/guides/SetReturningFunctions

The best answer is to define a row type for it with CREATE TYPE, but you
can also return setof record and rely on the user putting the type
information in the select.

On Thursday 27 February 2003 13:56, Kolus Maximiliano wrote:
> >     You can't return more than one return value from function, but that
> > value can be of composite type.For example :"setof text" or "setof
> > record" are such types.
>
>     Can you use functions returning "setof"s as if they were "normal"
> selects?.
>
>     I saw that setof takes a type or table as an argument. What if what
> i want to return is not in a table schema, can i do something like "setof
> (blah inet, blah2 varchar(256))"?


SETOF RECORD as shown at :
    http://developer.postgresql.org/docs/postgres/sql-select.html

or custom declared composite type:
    http://developer.postgresql.org/docs/postgres/sql-createtype.html