Loop on tables with PL/pgSQL in Postgres 9.0+
loops, plpgsql, postgresql, tablename, variables
Solution
The cursor returns a record, not a scalar value, so "tablename" is not a string variable.
The concatenation turns the record into a string that looks like this `(sql_features)`. If you had selected e.g. the schemaname with the tablename, the text representation of the record would have been `(public,sql_features)`.
So you need to access the column inside the record to create your SQL statement:
DO $$
DECLARE
tables CURSOR FOR
SELECT tablename
FROM pg_tables
WHERE tablename NOT LIKE 'pg_%'
ORDER BY tablename;
nbRow int;
BEGIN
FOR table_record IN tables LOOP
EXECUTE 'SELECT count(*) FROM ' || table_record.tablename INTO nbRow;
-- Do something with nbRow
END LOOP;
END$$;
You might want to use `WHERE schemaname = 'public'` instead of `not like 'pg_%'` to exclude the Postgres system tables.
Problem
I want to loop through all my tables to count rows in each of them. The following query gets me an error: ``` DO $$ DECLARE tables CURSOR FOR SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg_%' ORDER BY tablename; tablename varchar(100); nbRow int; BEGIN FOR tablename IN tables LOOP EXECUTE 'SELECT count(*) FROM ' || tablename INTO nbRow; -- Do something with nbRow END LOOP; END$$; ``` Errors: ``` ERROR: syntax error at or near ")" LINE 1: SELECT count(*) FROM (sql_features) ^ QUERY: SELECT count(*) FROM (sql_features) CONTEXT: PL/pgSQL function inline_code_block line 8 at EXECUTE statement ``` `sql_features` is a table's name in my DB. I already tried to use `quote_ident()` but to no avail.