Problem with Temp Table and Result Set from PL Function... - Mailing list pgsql-sql
From | devil live |
---|---|
Subject | Problem with Temp Table and Result Set from PL Function... |
Date | |
Msg-id | BAY20-F974B8CF7165F6CFBD7B6FEE880@phx.gbl Whole thread Raw |
Responses |
Re: Problem with Temp Table and Result Set from PL Function...
("devil live" <definite_ocean@hotmail.com>)
|
List | pgsql-sql |
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 charactervarying(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_nameAS 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_detailsds '|| ' 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_detailspot '|| ' WHERE stk.stock_code = '|| '''' || var_stock_code || ''''|| ' AND stk.stock_code= '|| ' CASE '|| ' WHEN (( SELECT ds.production_order '|| ' FROM production_operations_detailsds '|| ' 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/