Re: Returning a reference to a cursor from a function

On Thursday 12 Sep 2002 7:12 pm, david williams wrote:
> To anyone who can help me,
>
> I am new at Postgresql and am having some problems.

> I went a stage further attempt to put this query into a function as such
> CREATE FUNCTION getallusers() RETURN integer AS'
> DECLARE
> Liahona CURSOR FOR Select * from users;
> BEGIN
>
> FETCH FORWARD ALL IN Liahona;
>
> CLOSE Liahona;
> END;
> 'language 'plpgsql';

Number of issues here - it's RETURNS on the first line, you haven't OPENed the
cursor and I don't think you can use that form of FETCH in the function. Look
at the Programmers Guide, ch 23.7 "Cursors" for details on how to do it.

To get you going, here's one that returns a count of how many records were
fetched from an indicated table.

-- BEGIN function --
DROP FUNCTION foo_count(text);
CREATE FUNCTION foo_count(text) RETURNS integer AS '
DECLARE
my_name ALIAS FOR $1;

csr1 refcursor;
dummy RECORD;
n int4;
BEGIN
n:=0;
RAISE NOTICE ''counting table: %'',my_name;
OPEN csr1 FOR EXECUTE ''SELECT * FROM '' || my_name;
FETCH csr1 INTO dummy;
WHILE (FOUND) LOOP
n:=n+1;
FETCH csr1 INTO dummy;
END LOOP;
CLOSE csr1;

RETURN n;
END;
'language 'plpgsql';
-- END function --

Put this in a text-file and use \i filename from psql to import the definition
- makes it easier to debug.