Variables for identifiers inside IF EXISTS in a 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 variables in SELECT which is inside IF EXISTS? At the present moment, row.relname.tm and row.relname are treated literally which is not I want.

Traduce

Answer 1

CREATE OR REPLACE FUNCTION drop_now()
  RETURNS void
 LANGUAGE plpgsql 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$;

row is a reserved word in standard SQL. It's use is allowed in Postgres, but it's still unwise. I like to prepend PL/pgsql variable with an underscore _ to avoid naming conflicts.

You don't don't select the whole row anyway, just the table name in this example. Best use a variable of type regclass, thereby avoiding SQL injection by way of illegal table names implicitly. See:

• Table name as a PostgreSQL function parameter

You don't need LIMIT in an EXISTS expression, which only checks for the existence of any row. Also, you can just leave the SELECT list empty. See:

• What is easier to read in EXISTS subqueries?

You need dynamic SQL for queries with variable identifiers. Plain SQL does not allow for that. I.e.: build a query string and EXECUTE it. See:

• Dynamic SQL (EXECUTE) as condition for IF statement

The same is true for a DROP statement, should you want to run it. I added a comment.

Rostrum

One question: what does $f$ do in format? Can't find in the docs. – Traduce

@Tom: That's nested dollar-quoting. – Rostrum

Answer 2

You'll need to build your query as a string then execute that - see the section on executing dynamic commands in the plpgsql section of the manual.

Genus