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

plpgsqlpostgresql

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
SELECT * FROM validation();
I get an error:

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 –

The issue with your SQL is with the type (or return type).

In

SELECT * FROM validation();


You are using validation() as if the returned value from the function were a table. But you defined the function to return a single value

CREATE OR REPLACE FUNCTION validation()
RETURNS text AS 


So, the said function can't be used in the FROM clause.

If you are trying to use the side-effects of the function (PERFORM 'INSERT INTO ...), then you can use it in the SELECT list directly, e.g.

SELECT validation();


If you really intend to use the returned relation, then you need to make validation a Set Returning Function (SRF).

--EDIT--

In addition to the return type issue, another error in the IF statement is causing the actual error message (rather than the error in return type, which needs to be fixed as well).

If I understand what you are looking for (e.g. to test whether col3 = false for some rows in temp_table), one way that I think can achieve it, is to use a query, e.g.:

IF (SELECT 1 FROM temp_table WHERE  col3 = false) = 1 THEN
RAISE NOTICE 'there is a false value';
END IF;


The subquery (SELECT 1 FROM temp_table WHERE col3 = false) returns 1 if FALSE exists, and NULL otherwise.

I don't think it's valid to use temp_table.col3 directly.