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?
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;
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:
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:
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.
Always quote identifiers properly to defend against SQL injection and random errors. Schema and table have to be quoted separately! See:
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:
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:
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: