Re: Stored Procedure to return a result set

As I can see your function is returning set of rows so why dont you think about using cursor?
Using cursor will slove your problem

You can check the following :))
http://pgsqld.active-venture.com/plpgsql-cursors.html

With Regards
Ashish Karalkar
----- Original Message -----
From: Jasbinder Singh Bali
To: Rob Shepherd
Cc: pgsql-novice(at)postgresql(dot)org
Sent: Tuesday, February 06, 2007 9:12 AM
Subject: Re: [NOVICE] Stored Procedure to return a result set

On 1 Feb 2007 08:57:14 -0800, Rob Shepherd <rgshepherd(at)gmail(dot)com> wrote:
> Dear PG users,
>
> I'm attempting to create a stored procedure which returns a result set
> from the handset table to the caller.
>
> This stored proc will eventually be called by JDBC application. I'm
> using pgadmin to write and test.
>
> No luck so far. Here's what I have......
>
> CREATE OR REPLACE FUNCTION getunassigned(state integer)
> RETURNS SETOF macaddr AS
> $BODY$BEGIN
> SELECT mac FROM handsets WHERE state=$1;
> END;$BODY$
> LANGUAGE 'plpgsql' VOLATILE;
> ALTER FUNCTION getunassigned(state integer) OWNER TO "IPTHSACC";
>
I think your function should return set of handsets instead of macaddr.
What is macaddr here?

> when calling it via the query tool....
> IPTHsAcc=> SELECT * FROM getunassigned(1);
>
> I get an error thus....
> ERROR: SELECT query has no destination for result data
> HINT: If you want to discard the results, use PERFORM instead.
> CONTEXT: PL/pgSQL function "getunassigned" line 2 at SQL statement
>
> Please could somebody show me a simple example of a stored proc/func
> which returns a set.
>

CREATE FUNCTION getfoo(int) RETURNS setof foo AS '
SELECT * FROM foo WHERE fooid = $1;
' LANGUAGE SQL;

> my table is....
>
> CREATE TABLE handsets (
> mac macaddr NOT NULL,
> state smallint DEFAULT 0 NOT NULL
> );
>
>
> many thanks for any pointers.
>
> Rob
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>