Dynamic SELECT INTO in PL/pgSQL function

Karthik picture Karthik · Sep 1, 2017 · Viewed 9k times · Source

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?

Answer

Erwin Brandstetter picture Erwin Brandstetter · Sep 2, 2017
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


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: