From: devil live <definite_ocean@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Problem with Temp Table and Result Set from PL Function...
Date: Wed, 21 Feb 2007 11:31:02 +0000
Message-ID: <BAY20-F974B8CF7165F6CFBD7B6FEE880@phx.gbl> (raw)
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: definite_ocean@hotmail.com
Subject: Re: Problem with Temp Table and Result Set from PL Function...
In-Reply-To: <BAY20-F974B8CF7165F6CFBD7B6FEE880@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox