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

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

Problem with Temp Table and Result Set from PL Function...
2+ messages / 1 participants
[nested] [flat]

* Problem with Temp Table and Result Set from PL Function...
@ 2007-02-21 11:31 devil live <definite_ocean@hotmail.com>
  2007-02-21 11:52 ` Re: Problem with Temp Table and Result Set from PL Function... devil live <definite_ocean@hotmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: devil live @ 2007-02-21 11:31 UTC (permalink / raw)
  To: 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 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/




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Problem with Temp Table and Result Set from PL Function...
  2007-02-21 11:31 Problem with Temp Table and Result Set from PL Function... devil live <definite_ocean@hotmail.com>
@ 2007-02-21 11:52 ` devil live <definite_ocean@hotmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: devil live @ 2007-02-21 11:52 UTC (permalink / raw)
  To: 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/




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2007-02-21 11:52 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-21 11:31 Problem with Temp Table and Result Set from PL Function... devil live <definite_ocean@hotmail.com>
2007-02-21 11:52 ` devil live <definite_ocean@hotmail.com>

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