Postgresql - psql - for loop not displaying output

Question

The following sql file runs without error; however it does no display any output. The file is executed with: pslq -d database -f filename. How can this be modified to display output?

do $$
  declare tn varchar ;
  begin
     for tn in 
        select tablename from pg_tables where tableowner not like 'postgres%'
        loop
            EXECUTE format('SELECT count(*) from  %I;', tn);
        end loop;
  end;
$$;

A `do` block can not return anything. you don't need PL/pgSQL to achieve this. See e.g. here: https://stackoverflow.com/a/38684225/ — , Feb 01 '22 at 19:52
Reading the docs would be a start [DO](https://www.postgresql.org/docs/current/sql-do.html): "The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.' . And [plpgsql looping](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING). And/or [Returning](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING) in particular `RETURN QUERY`. — Adrian Klaver, Feb 01 '22 at 19:55

score 0 · Answer 1 · answered Feb 02 '22 at 05:37

The problem is that the result of a dynamic query is discarded unless you use SELECT ... INTO:

DO $$
DECLARE
   tn text;
   total bigint;
BEGIN
   FOR tn IN 
      SELECT tablename FROM pg_tables WHERE tableowner NOT LIKE 'postgres%'
   LOOP
      EXECUTE format('SELECT count(*) from  %I;', tn) INTO total;
      RAISE NOTICE 'Table: % rows: %', tn, total;
   END LOOP;
END;
$$;

Postgresql - psql - for loop not displaying output

1 Answers1