Re: Problem with Temp Table and Result Set from PL Function... - Mailing list pgsql-sql
From | devil live |
---|---|
Subject | Re: Problem with Temp Table and Result Set from PL Function... |
Date | |
Msg-id | BAY20-F1162A2AC9CCB705503532CEE880@phx.gbl Whole thread Raw |
In response to | Problem with Temp Table and Result Set from PL Function... ("devil live" <definite_ocean@hotmail.com>) |
List | pgsql-sql |
I solved the problem as select * from MYFUNC(); instead of select MYFUNC(); >From: "devil live" <definite_ocean@hotmail.com> >To: pgsql-sql@postgresql.org >Subject: [SQL] Problem with Temp Table and Result Set from PL Function... >Date: Wed, 21 Feb 2007 11:31:02 +0000 > > > >When I run following Function with the following SETOF table... > >it says: > >NOTICE: table "temp_production_product_operations" does not exist, >skipping >CONTEXT: SQL statement "drop table if exists >temp_production_product_operations " >PL/pgSQL function "pb_report_temp_gerceklesme12" line 11 at execute >statement > >ERROR: set-valued function called in context that cannot accept a set >SQL state: 0A000 >Context: PL/pgSQL function "pb_report_temp_gerceklesme12" line 56 at return >next > >Where is the error? I could not determined... > > > >CREATE TABLE temp_temp_production_operations >( > product_code character varying(25), > product_name character varying(255), > production_order bigint, > stock_code character varying(25), > operation_code bigint >) ; > > >=================================================== >DROP FUNCTION pb_report_temp_gerceklesme12 ( character varying, character >varying); > >CREATE OR REPLACE FUNCTION pb_report_temp_gerceklesme12(character varying, >character varying) > RETURNS SETOF temp_temp_production_operations AS >$BODY$ > >DECLARE >tmp RECORD; >var_stock_code ALIAS FOR $1; >var_production_order ALIAS FOR $2 ; > >BEGIN > >-- drop temp table, if it exists (ignore exception if it doesn't) > begin > execute 'drop table if exists temp_production_product_operations ' ; > > exception > when undefined_table then > null; -- do nothing > end; > > > > EXECUTE 'create temp table temp_production_product_operations AS ' > || ' SELECT stk.stock_code AS product_code, stk.stock_name AS >product_name, ' > || ' NULL::integer AS production_order, pot.stock_code, pot.operation_code > ' > || ' FROM stock stk, production_operations_template pot ' > || ' WHERE stk.stock_code = ' > || '''' || var_stock_code || '''' > || ' AND stk.stock_code = ' > || ' CASE ' > || ' WHEN (( SELECT ds.production_order ' > || ' FROM production_operations_details ds ' > || ' WHERE ds.product_code = stk.stock_code AND ds.production_order = ' > || '''' || var_production_order || '''' > || ' LIMIT 1)) IS NULL THEN pot.product_code ' > || ' ELSE ''''::varchar ' > || ' END ' > || ' UNION ' > || ' SELECT stk.stock_code AS product_code, stk.stock_name AS >product_name, ' > || ' pot.production_order, pot.stock_code, pot.operation_code ' > || ' FROM stock stk, production_operations_details pot ' > || ' WHERE stk.stock_code = ' > || '''' || var_stock_code || '''' > || ' AND stk.stock_code = ' > || ' CASE ' > || ' WHEN (( SELECT ds.production_order ' > || ' FROM production_operations_details ds ' > || ' WHERE ds.product_code = stk.stock_code AND ' > || ' ds.production_order = ' > || '''' || var_production_order || '''' > || ' LIMIT 1)) IS NOT NULL ' > || ' THEN pot.product_code ' > || ' ELSE ''''::varchar ' > || ' END ORDER BY 1, 2, 4 '; > > -- temp_production_product_operations : WE created as temp above... > FOR tmp IN EXECUTE 'SELECT * FROM temp_production_product_operations' > LOOP > RETURN NEXT tmp; > END LOOP; > > >RETURN; > >end; >$BODY$ > LANGUAGE 'plpgsql'; >=============================================== > >_________________________________________________________________ >En etkili ve g�venilir PC Korumayi tercih edin, rahat edin! >http://www.msn.com.tr/security/ > > >---------------------------(end of broadcast)--------------------------- >TIP 4: Have you searched our list archives? > > http://archives.postgresql.org _________________________________________________________________ Hava durumunu bizden �grenin ve evden �yle �ikin! http://www.msn.com.tr/havadurumu/