Skip site navigation (1) Skip section navigation (2)

Re: Stored Procedure to return a result set

From: "Jasbinder Singh Bali" <jsbali(at)gmail(dot)com>
To: "Rob Shepherd" <rgshepherd(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Stored Procedure to return a result set
Date: 2007-02-06 03:42:33
Message-ID: a47902760702051942h50630a64gfd28fc5566491a5c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
>

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2007-02-06 05:18:05
Subject: Re: pg_hba.conf, md5 and .pgpass
Previous:From: fbnDate: 2007-02-05 16:55:30
Subject: Composed Key and autoincrement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group