Re: 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
>