Solución:
No recuerdo la última vez que necesité usar un cursor explícito para hacer un bucle en plpgsql.
Utilice el cursor implícito de un FOR bucle, eso es mucho más limpio:
DO
$$
DECLARE
rec record;
nbrow bigint;
BEGIN
FOR rec IN
SELECT *
FROM pg_tables
WHERE tablename NOT LIKE 'pg_%'
ORDER BY tablename
LOOP
EXECUTE 'SELECT count(*) FROM '
|| quote_ident(rec.schemaname) || '.'
|| quote_ident(rec.tablename)
INTO nbrow;
-- Do something with nbrow
END LOOP;
END
$$;
Debe incluir el nombre del esquema para que esto funcione para todos los esquemas (incluidos los que no están en su search_path).
Además, en realidad necesitar usar quote_ident() o format() con %I o un regclass variable para protegerse contra la inyección de SQL. El nombre de una tabla puede ser casi cualquier cosa entre comillas dobles. Ver:
- Nombre de la tabla como parámetro de función de PostgreSQL
Detalle menor: escapar del guión bajo (_) en el LIKE patrón para que sea un literal guion bajo: tablename NOT LIKE 'pg_%'
Cómo podría hacerlo:
DO
$$
DECLARE
tbl regclass;
nbrow bigint;
BEGIN
FOR tbl IN
SELECT c.oid
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND n.nspname NOT LIKE 'pg_%' -- system schema(s)
AND n.nspname <> 'information_schema' -- information schema
ORDER BY n.nspname, c.relname
LOOP
EXECUTE 'SELECT count(*) FROM ' || tbl INTO nbrow;
-- raise notice '%: % rows', tbl, nbrow;
END LOOP;
END
$$;
Consulta pg_catalog.pg_class en lugar de tablename, proporciona el OID de la tabla.
El tipo de identificador de objeto regclass es útil para simplificar. En particular, los nombres de las tablas se citan dos veces y se califican por esquema cuando es necesario de forma automática (también evita la inyección de SQL).
Esta consulta también excluye tablas temporales (el esquema temporal se denomina pg_temp% internamente).
Para incluir solo tablas de un esquema determinado:
AND n.nspname="public" -- schema name here, case-sensitive
El cursor devuelve un registro, no un valor escalar, por lo que “tablename” no es una variable de cadena.
La concatenación convierte el registro en una cadena que se ve así (sql_features). Si hubiera seleccionado, por ejemplo, el esquema con el nombre de la tabla, la representación de texto del registro habría sido (public,sql_features).
Por lo tanto, debe acceder a la columna dentro del registro para crear su declaración SQL:
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$$;
Es posible que desee utilizar WHERE schemaname="public" en lugar de not like 'pg_%' para excluir las tablas del sistema Postgres.