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
Last activity: Jun 29, 2025, 04:08 AM