'Postgresql - psql - for loop not displaying output

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;
$$;
postgresqlpsql


Solution 1:[1]

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;
$$;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Laurenz Albe

Related Questions