Quick Links

Re: Stored Procedure to return a result set

From:	Sue Fitt <sue(at)inf(dot)ed(dot)ac(dot)uk>
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 10:18:08
Message-ID:	45C855E0.5020007@inf.ed.ac.uk
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-novice

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Rob, 
 
Think I can answer this one, but I count myself as a novice so I don't
guarantee there's no mistakes here. 
Firstly you need to use EXECUTE 'SELECT', secondly you need to return
the set, e.g. from one of mine 
 
<pre wrap=""> CREATE OR REPLACE FUNCTION getunassigned(state integer)
RETURNS SETOF macaddr AS
$BODY
$DECLARE
i record;
myvalue macaddr%rowtype;

$BEGIN
FOR i in EXECUTE 'SELECT mac FROM handsets WHERE state=$1' LOOP
RETURN NEXT myvalue;
END LOOP;
RETURN;

END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;</pre>
I'm not sure what $1 is referring to so I don't know how to quote that,
and I'm assuming maccaddr is a rowtype but perhaps you've defined it as
something else. Here's one of mine that I know works.
return_expanded_fields_for_func is a user-defined rowtype. 
 
<meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8">
<title></title>
<meta name="GENERATOR" content="OpenOffice.org 2.0 (Linux)">
<meta name="CREATED" content="20070206;9585800">
<meta name="CHANGED" content="16010101;0">
<style>

</style>
    CREATE FUNCTION
add_collocation(mywords
text, myhos text, mysem text) RETURNS SETOF
        return_expanded_fields_for_func AS $$
    DECLARE 
        tmpsem text = mysem; 
        tmpwords text = mywords; 
        ret
return_expanded_fields_for_func%rowtype;
    BEGIN 
       --escape quotes 
         tmpsem =
regexp_replace(tmpsem, '\'', '\\\'', 'g'); 
        tmpwords =
regexp_replace(tmpwords, '\'', '\\\'', 'g'); 
        
        --this function adds to tables 
        EXECUTE 'SELECT * FROM add_entry
(\'collocations\', \'' || tmpwords || '\', \'' || myhos || '\', \'\',
\'' || tmpsem || '\', \'\', \'\', \'\', \'\', \'\')'; 
        --now output what we've done, using fields from a view on those
tables 
        EXECUTE 'CREATE TEMP TABLE pron_tmp
AS SELECT * FROM show_all_fields_expanded_refs WHERE headword = \''
|| tmpwords || '\'';
        FOR ret IN EXECUTE 'SELECT *
FROM
pron_tmp ORDER BY ho, vo' LOOP 
                RETURN NEXT ret; 
        END LOOP;
 
        DROP TABLE pron_tmp;
        RETURN;
    END; 
    $$ 
    LANGUAGE plpgsql;
 
HTH, 
Sue Fitt 
Rob Shepherd wrote:
<blockquote
cite="mid1170349034(dot)900196(dot)235690(at)m58g2000cwm(dot)googlegroups(dot)com"
type="cite">
<pre wrap="">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";

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.

Rob

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

</pre>
</blockquote>
</body>
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	4.7 KB

In response to

Stored Procedure to return a result set at 2007-02-01 16:57:14 from Rob Shepherd

Browse pgsql-novice by date

	From	Date	Subject
Next Message	Jasbinder Singh Bali	2007-02-06 13:08:30	Re: Postgre Connection question
Previous Message	joe speigle	2007-02-06 10:11:42	Re: Composed Key and autoincrement