Re: PostgreSQL Functions / PL-Language

On Sat, Feb 18, 2006 at 04:48:55PM +0800, Jan Cruz wrote:
> > > Why does my stored function returns only the first row of the query
> > > instead of the whole set of query?
> >
> > Did you declare your function to return "setof <whatever>" ?
>
> I did but I don't know if I have to iterate/loop and use "return next
> setof_foo" or just use return next as it is.

Is the function SQL or PL/pgSQL? Here's an example of each:

CREATE TABLE foo (id integer, t text);
INSERT INTO foo VALUES (1, 'one');
INSERT INTO foo VALUES (2, 'two');

CREATE FUNCTION func1() RETURNS SETOF foo AS $$
SELECT * FROM foo;
$$ LANGUAGE sql STABLE;

CREATE FUNCTION func2() RETURNS SETOF foo AS $$
DECLARE
row foo%ROWTYPE;
BEGIN
FOR row IN SELECT * FROM foo LOOP
RETURN NEXT row;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql STABLE;

SELECT * FROM func1();
id | t
----+-----
1 | one
2 | two
(2 rows)

SELECT * FROM func2();
id | t
----+-----
1 | one
2 | two
(2 rows)

--
Michael Fuhr