Dynamic SELECT INTO in PL/pgSQL function

Question

How can I write a dynamic SELECT INTO query inside a PL/pgSQL function in Postgres?

Say I have a variable called tb_name which is filled in a FOR loop from information_schema.tables. Now I have a variable called tc which will be taking the row count for each table. I want something like the following:

FOR tb_name in select table_name from information_schema.tables where table_schema='some_schema' and table_name like '%1%'
LOOP
EXECUTE FORMAT('select count(*) into' || tc 'from' || tb_name);
END LOOP

What should be the data type of tb_name and tc in this case?

Solution 1

CREATE OR REPLACE FUNCTION myfunc(_tbl_pattern text, _schema text = 'public')
  RETURNS void AS  -- or whatever you want to return
$func$
DECLARE
   _tb_name information_schema.tables.table_name%TYPE;  -- currently varchar
   _tc      bigint;  -- count() returns bigint
BEGIN
   FOR _tb_name IN
      SELECT table_name
      FROM   information_schema.tables
      WHERE  table_schema = _schema
      AND    table_name   ~ _tbl_pattern  -- see below!
   LOOP
      EXECUTE format('SELECT count(*) FROM %I.%I', _schema, _tb_name)
      INTO _tc;      

      -- do something with _tc
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Notes

• I prepended all parameters and variables with an underscore (_) to avoid naming collisions with table columns. Just a useful convention.

• _tc should be bigint, since that's what the aggregate function count() returns.

• The data type of _tb_name is derived from its parent column dynamically: information_schema.tables.table_name%TYPE. See the chapter Copying Types in the manual.

• Are you sure you only want tables listed in information_schema.tables? Makes sense, but be aware of implications. See:

  • How to check if a table exists in a given schema

• a_horse already pointed to the manual and Andy provided a code example. This is how you assign a single row or value returned from a dynamic query with EXECUTE to a (row) variable. A single column (like count in the example) is decomposed from the row type automatically, so we can assign to the scalar variable tc directly - in the same way we would assign a whole row to a record or row variable. Related:

  • How to get the value of a dynamically generated field name in PL/pgSQL

• Schema-qualify the table name in the dynamic query. There may be other tables of the same name in the current search_path, which would result in completely wrong (and very confusing!) results without schema-qualification. Sneaky bug! Or this schema is not in the search_path at all, which would make the function raise an exception immediately.

  • How does the search_path influence identifier resolution and the "current schema"

• Always quote identifiers properly to defend against SQL injection and random errors. Schema and table have to be quoted separately! See:

  • Table name as a PostgreSQL function parameter
  • Truncating all tables in a Postgres database

• I use the regular expression operator ~ in table_name ~ _tbl_pattern instead of table_name LIKE ('%' || _tbl_pattern || '%'), that's simpler. Be wary of special characters in the pattern parameter either way! See:

  • PostgreSQL Reverse LIKE
  • Escape function for regular expression or LIKE patterns
  • Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

• I set a default for the schema name in the function call: _schema text = 'public'. Just for convenience, you may or may not want that. See:

  • Assigning default value for type

---

Addressing your comment: to pass values, use the USING clause like:

EXECUTE format('SELECT count(*) FROM %I.%I
                WHERE some_column = $1', _schema, _tb_name,column_name)
USING user_def_variable;

Related:

• INSERT with dynamic table name in trigger function

Solution 2

It looks like you want the %I placeholder for FORMAT so that it treats your variable as an identifier. Also, the INTO clause should go outside the prepared statement.

FOR tb_name in select table_name from information_schema.tables where table_schema='some_schema' and table_name like '%1%'
LOOP
  EXECUTE FORMAT('select count(*) from %I', tb_name) INTO tc;
END LOOP

Comments

[a_horse_with_no_name]

See the examples in the manual: postgresql.org/docs/current/static/…

[Erwin Brandstetter]

It would be a sneaky bug to use the table name without schema-qualification. I added some explanation.