Sample Header Ad - 728x90

using PERFORM to insert a string of SELECT statement into a temp table

0 votes
1 answer
176 views
I am trying to insert data into a temp_table and then truncating the table after analyzing the result. Here is my code: CREATE OR REPLACE FUNCTION validation() RETURNS text AS $$ DECLARE counter INTEGER; DECLARE minsid INTEGER; DECLARE maxsid INTEGER; DECLARE rec RECORD; DECLARE stmt varchar; BEGIN SELECT MIN(sid) INTO minsid FROM staging.validation; SELECT MAX(sid) INTO maxsid FROM staging.validation; CREATE TEMPORARY TABLE temp_table (col1 TEXT, col2 INTEGER, col3 BOOLEAN) ON COMMIT DROP; FOR counter IN minsid..maxsid LOOP RAISE NOTICE 'Counter: %', counter; SELECT sql INTO stmt FROM staging.validation WHERE sid = counter; RAISE NOTICE 'sql: %', stmt; PERFORM 'INSERT INTO temp_table (col1, col2, col3) ' || stmt; IF temp_table.col3 = false THEN RAISE NOTICE 'there is a false value'; END IF; END LOOP; END; $$ LANGUAGE plpgsql; Whenever I run this function
* FROM validation();
I get an error:
: missing FROM-clause entry for table "temp_table" Where: PL/pgSQL function validation() line 21 at IF
Here is how my staging.validation table looks - https://docs.google.com/spreadsheets/d/1bXO9gqFS-GtcC1qJtgNbFkR6ygOuPtR_RZoU7VNhgrI/edit?usp=sharing
Asked by hky404 (313 rep)
Sep 15, 2017, 06:28 PM
Last activity: Jun 29, 2025, 04:08 AM