[SQL] Problem with Temp Table and Result Set from PL Function...
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
Re: [SQL] 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" <[EMAIL PROTECTED]> 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 + 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/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] how to generate a list of distinct scalar values from a column which type is array
Dear all, given a column which type is for instance varchar(20)[], is there any SQL command that let me generate the list of distinct scalar values? e.g.: col1 row 1: (aaa, bb, c) row 2: (, ) row 3: (aaa, ) the query should return: aaa bb c if not, I guess the approach is to use stored procedure. Any code snippet/suggestion? Thanks, Sergio
[SQL] Problems with temp table and PL
I create a temp table inside a plpgsql function, which is drop just before ending (the function). My problem is that if I execute the same function again (in the same session) I get an error when trying to insert data into it (looks like the session has an old reference of the table): => SELECT actualizacionAnualProximoHabil(2008); ERROR: relation with OID 9668312 does not exist CONTEXT: SQL statement "INSERT INTO dias_semana VALUES ( $1 )" PL/pgSQL function "actualizacionanualproximohabil" line 9 at SQL statement As I said, the first execution works OK, but from then on it gives this error, until I close the session and open it again. Any ideas? -- 21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18 - Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática| '@' || 'unl.edu.ar'; Universidad Nacional| DBA, Programador, del Litoral | Administrador - ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Problems with temp table and PL
On Wed, Feb 21, 2007 at 06:17:50PM -0300, Martin Marques wrote: > My problem is that if I execute the same function again (in the same > session) I get an error when trying to insert data into it (looks like the > session has an old reference of the table): You have to use EXECUTE for this. See the docs. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Problems with temp table and PL
am Wed, dem 21.02.2007, um 18:17:50 -0300 mailte Martin Marques folgendes: > I create a temp table inside a plpgsql function, which is drop just before > ending (the function). > > My problem is that if I execute the same function again (in the same > session) I get an error when trying to insert data into it (looks like the > session has an old reference of the table): Right. Use EXECUTE for DDL-commands. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
