SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
05-16-2008, 01:42 PM
| ||||
| ||||
 Re: Howto return values from a function > How you generate the results is up to you. when you have them you > either use RETURN NEXT or RETURN QUERY to return them to the caller. Now I get the reply ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "actionlist" line 11 at return next and here is the function (and a datatype that is used for the return values) CREATE TYPE Ttelnr_action AS ( nr VARCHAR(30), action CHAR(1) ); CREATE OR REPLACE FUNCTION actionlist(tid_ TIMESTAMP) RETURNS SETOF Ttelnr_action AS $$ DECLARE rec RECORD; result Ttelnr_action; BEGIN FOR rec IN SELECT DISTINCT custid,nr,action FROM Actions LOOP IF rec.action = 'view_important_message' THEN result.nr := rec.nr; result.action := 'd'; RETURN NEXT result; ELSIF rec.action = 'download_movie' THEN result.nr := rec.nr; result.action := 'v'; RETURN NEXT result; END IF; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general      |
|
05-16-2008, 01:42 PM
| |||
| |||
| Re: Howto return values from a function Hello use SELECT * FROM actionlist(...); Regards Pavel Stehule 2008/5/16 A B <gentosaker@gmail.com>: >> How you generate the results is up to you. when you have them you >> either use RETURN NEXT or RETURN QUERY to return them to the caller. > > Now I get the reply > > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "actionlist" line 11 at return next > > and here is the function (and a datatype that is used for the return values) > > > CREATE TYPE Ttelnr_action AS ( > nr VARCHAR(30), > action CHAR(1) > ); > > CREATE OR REPLACE FUNCTION actionlist(tid_ TIMESTAMP) RETURNS SETOF > Ttelnr_action AS $$ > DECLARE > rec RECORD; > result Ttelnr_action; > BEGIN > FOR rec IN SELECT DISTINCT custid,nr,action FROM Actions > LOOP > IF rec.action = 'view_important_message' THEN > result.nr := rec.nr; > result.action := 'd'; > RETURN NEXT result; > ELSIF rec.action = 'download_movie' THEN > result.nr := rec.nr; > result.action := 'v'; > RETURN NEXT result; > END IF; > END LOOP; > RETURN; > END; > $$ LANGUAGE plpgsql; > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general |
|
05-16-2008, 01:42 PM
| |||
| |||
| Re: Howto return values from a function A B wrote: >> How you generate the results is up to you. when you have them you >> either use RETURN NEXT or RETURN QUERY to return them to the caller. > > Now I get the reply > > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "actionlist" line 11 at return next It's a source of rows, so you need to treat it like a table or a view: SELECT * FROM actionlist(...); -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general |
|
05-16-2008, 01:42 PM
| ||||
| ||||
| Re: Howto return values from a function Great!  That did the trick!Thank you so very much! 2008/5/16 Richard Huxton <dev@archonet.com>: > A B wrote: >>> >>> How you generate the results is up to you. when you have them you >>> either use RETURN NEXT or RETURN QUERY to return them to the caller. >> >> Now I get the reply >> >> ERROR: set-valued function called in context that cannot accept a set >> CONTEXT: PL/pgSQL function "actionlist" line 11 at return next > > It's a source of rows, so you need to treat it like a table or a view: > > SELECT * FROM actionlist(...); > > > -- > Richard Huxton > Archonet Ltd > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general |
Linear Mode
