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

Re: Stored Procedure to return a result set

From: "Ashish Karalkar" <ashish(dot)karalkar(at)info-spectrum(dot)com>
To: "Jasbinder Singh Bali" <jsbali(at)gmail(dot)com>, "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 07:11:42
Message-ID: 002501c749be$10e05660$170211ac@LIONKING.COM (view raw or flat)
Thread:
Lists: pgsql-novice
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
  > 
   

In response to

pgsql-novice by date

Next:From: Shane AmblerDate: 2007-02-06 09:12:48
Subject: Re: Postgre Connection question
Previous:From: Tom LaneDate: 2007-02-06 05:23:39
Subject: Re: Composed Key and autoincrement

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