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

Stored Procedure to return a result set

From: "Rob Shepherd" <rgshepherd(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Stored Procedure to return a result set
Date: 2007-02-01 16:57:14
Message-ID: (view raw or whole thread)
Lists: pgsql-novice
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)
        SELECT mac FROM handsets WHERE state=$1;
ALTER FUNCTION getunassigned(state integer) OWNER TO "IPTHSACC";

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.

my table is....

CREATE TABLE handsets (
    mac macaddr NOT NULL,
    state smallint DEFAULT 0 NOT NULL

many thanks for any pointers.



pgsql-novice by date

Next:From: Matthew CampbellDate: 2007-02-01 16:58:08
Subject: Re: Newbie Developer Question
Previous:From: Tom LaneDate: 2007-02-01 16:47:05
Subject: Re: [Fwd: query efficiency - Can I speed it up?]

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