Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: "david williams" <dw_remote@hotmail.com>,
	<pgsql-sql@postgresql.org>
Subject: Re: Returning a reference to a cursor from a function
Date: Tue, 17 Sep 2002 16:29:56 +0100
User-Agent: KMail/1.4.1
References: <DAV374EsURKQaJcrJf00000cd8c@hotmail.com>
In-Reply-To: <DAV374EsURKQaJcrJf00000cd8c@hotmail.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200209171629.57041.dev@archonet.com>

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=20
cursor and I don't think you can use that form of FETCH in the function. Lo=
ok=20
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=
=20
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:=3D0;
    RAISE NOTICE ''counting table: %'',my_name;
    OPEN csr1 FOR EXECUTE ''SELECT * FROM '' || my_name;
    FETCH csr1 INTO dummy;
    WHILE (FOUND) LOOP
        n:=3Dn+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 definit=
ion=20
- makes it easier to debug.