Thread: Stored Procedure to return a result set
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
On 1 Feb 2007 08:57:14 -0800, Rob Shepherd <rgshepherd@gmail.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;
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
>
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 :))
With Regards
Ashish Karalkar
----- Original Message -----From: Jasbinder Singh BaliTo: Rob ShepherdSent: Tuesday, February 06, 2007 9:12 AMSubject: Re: [NOVICE] Stored Procedure to return a result set
On 1 Feb 2007 08:57:14 -0800, Rob Shepherd <rgshepherd@gmail.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
>
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 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; 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. <!-- @page { size: 21cm 29.7cm; margin: 2cm } P { margin-bottom: 0.21cm } --> 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.900196.235690@m58g2000cwm.googlegroups.com" type="cite"> 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