Thread: PostgreSQL Functions / PL-Language

Correct me if I am wrong but SQL procedural language doesn't have support for variable declarations?

And

Why does my stored function returns only the first row of the query instead of the whole set of query?

Kindly educate me :)

On Sat, Feb 18, 2006 at 04:10:13PM +0800, Jan Cruz wrote:
> Correct me if I am wrong but SQL procedural language doesn't have support
> for variable declarations?

Correct, you'll need pl/pgsql for that.

> 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>" ?

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

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.

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

Thanks for the correct syntaxing Mike.

BTW I also got something like this:

CREATE FUNCTION func2() RETURNS SETOF foo as $$
DECLARE
row foo;
BEGIN
  SELECT INTO ROW * from FOO;
   return next foo;
END;
$$ LANGUAGE plpgsql STABLE;

select * from func2();

It did return the 2 rows (all rows) when I first test it.
Then today I tried the same function  and test it then it return only 1 row.

I wonder..........

BTW, I'm using PostgreSQL 8.1.3.

On Mon, Feb 20, 2006 at 02:36:04PM +0800, Jan Cruz wrote:
> BTW I also got something like this:
>
> CREATE FUNCTION func2() RETURNS SETOF foo as $$
> DECLARE
> row foo;
> BEGIN
>   SELECT INTO ROW * from FOO;
>    return next foo;
> END;
> $$ LANGUAGE plpgsql STABLE;

Please post the actual code instead of "something like" it.  Trying
to create the above function fails; I'm guessing you really have
"return next row" instead of "return next foo".

> select * from func2();
>
> It did return the 2 rows (all rows) when I first test it.
> Then today I tried the same function  and test it then it return only 1 row.

As written the function above should return only one row because
it doesn't loop through the results.  I suspect the difference
between the earlier test and the most recent one is that you removed
the loop.

--
Michael Fuhr