From: Richard Huxton <dev@archonet.com>
To: david williams <dw_remote@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Returning a reference to a cursor from a function
Date: Tue, 17 Sep 2002 16:29:56 +0100
Message-ID: <200209171629.57041.dev@archonet.com> (raw)
In-Reply-To: <DAV374EsURKQaJcrJf00000cd8c@hotmail.com>
References: <DAV374EsURKQaJcrJf00000cd8c@hotmail.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
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, dw_remote@hotmail.com
Subject: Re: Returning a reference to a cursor from a function
In-Reply-To: <200209171629.57041.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox