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