Variables for identifiers inside IF EXISTS in the plpgsql function

Question

CREATE OR REPLACE FUNCTION drop_now() RETURNS void AS $BODY$ DECLARE row record; BEGIN RAISE INFO 'in'; FOR row IN select relname from pg_stat_user_tables WHERE schemaname='public' AND relname LIKE '%test%' LOOP IF EXISTS(SELECT row.relname.tm FROM row.relname WHERE row.relname.tm < current_timestamp - INTERVAL '90 minutes' LIMIT 1) THEN -- EXECUTE 'DROP TABLE ' || quote_ident(row.relname); RAISE INFO 'Dropped table: %', quote_ident(row.relname); END IF; END LOOP; END; $BODY$ LANGUAGE plpgsql VOLATILE; 

Could you tell me how to use the variables in SELECT that are inside IF EXISTS ? Currently row.relname.tm and row.relname are row.relname processed literally, which I don't want.

Answer 1

 CREATE OR REPLACE FUNCTION drop_now() RETURNS void AS $func$ DECLARE _tbl regclass; _found int; BEGIN FOR _tbl IN SELECT relid FROM pg_stat_user_tables WHERE schemaname = 'public' AND relname LIKE '%test%' LOOP EXECUTE format($f$SELECT 1 FROM %s WHERE tm < now() - interval '90 min'$f$, _tbl); GET DIAGNOSTICS _found = ROW_COUNT; IF _found > 0 THEN -- EXECUTE 'DROP TABLE ' || _tbl; RAISE NOTICE 'Dropped table: %', _tbl; END IF; END LOOP; END $func$ LANGUAGE plpgsql; 

Highlights

• row is a reserved word in the SQL standard. This use is permitted in Postgres, but it is still unreasonable. I am making it a habit to add a psql variable with an underscore _ to avoid name conflicts.

• You still don't select the whole row, just the table name in this example. It is best to use a variable of type regclass , thereby automatically avoiding SQL injection through illegal table names. Details in this answer:
  Table name as parameter of PostgreSQL function

• You do not need a LIMIT in an EXISTS expression that checks for any rows. And for the same reason, you do not need meaningful target columns. Just write SELECT 1 or SELECT * or something else .

• For queries with variable identifiers, dynamic SQL is required. Normal SQL does not allow this. Ie: build a query string and EXECUTE it. Details in this closely related answer:
  Dynamic SQL (EXECUTE) as a condition for an IF statement

• The same is true for the DROP statement if you want to run it. I have added a comment.

Answer 2

You will need to build your query as a string, and then execute this - see the section on executing dynamic commands in the plpgsql section of the manual .