Re: Problem with Temp Table and Result Set from PL Function...

I solved the problem as

select * from MYFUNC();
instead of
select MYFUNC();

>From: "devil live" <definite_ocean(at)hotmail(dot)com>
>To: pgsql-sql(at)postgresql(dot)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 gvenilir 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/