From: | Antti Haapala <antti(dot)haapala(at)iki(dot)fi> |
---|---|
To: | Kolus Maximiliano <Kolus(dot)maximiliano(at)bcr(dot)com(dot)ar> |
Cc: | Darko Prenosil <darko(dot)prenosil(at)finteh(dot)hr>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: SETOF (was: Function example returning more then 1 |
Date: | 2003-02-27 14:29:59 |
Message-ID: | Pine.GSO.4.44.0302271617440.22197-100000@paju.oulu.fi |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adam Harnett | 2003-02-27 14:57:59 | Pg_dump Help |
Previous Message | Peter Gibbs | 2003-02-27 14:13:16 | Re: PGSQL function question |