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

Question

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 -

https://docs.google.com/spreadsheets/d/1bXO9gqFS-GtcC1qJtgNbFkR6ygOuPtR_RZoU7VNhgrI/edit?usp=sharing

Answer 1

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.

• hky404: I think the issue is with IF condition, as even SELECT validation(); gives me the same error.
• tinlyx: @hky404 You're right. It seems that another error which directly causes the error message. Please see my edit.