CODEWITHSUNDEEP
postgresqlcountplpgsqldynamic-sql
Rémi Desgrange
Rémi Desgrange

Reputation: 908

Handle result when dynamic SQL is in a loop

I have a bunch of table that have a "stat" column (stat for status ;-)

I would like the count of each stats, and see it!

My tables look like this

create table a (
   a_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
   a_stat status_t
);
create table b (
   b_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
   b_stat status_t
);

status_t is an enum.

So I did this:

DO $$
DECLARE
  tableName RECORD;
  result RECORD;
BEGIN
  SET SEARCH_PATH = projet, public;

  FOR tableName IN SELECT
             c.relname,
             a.attname
           FROM pg_class AS c
             INNER JOIN pg_attribute AS a ON a.attrelid = c.oid
           WHERE a.attname LIKE '%stat' AND c.relkind = 'r' LOOP

      EXECUTE format('SELECT %I, count(%I) FROM %I GROUP BY %I',
                     tableName.attname, tableName.attname, tableName.relname, tableName.attname) INTO result;
      SELECT * FROM result;
  END LOOP;
END;
$$;

There are things that I think I'm not doing well here.

How to do this properly?

Upvotes: 1

Views: 1527

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656153

A DO command has no provision to return data. You could raise notices or write to a temporary table. But rather use a proper function instead:

CREATE OR REPLACE FUNCTION foo()
  RETURNS TABLE (
    sch_name  text
  , tbl_name  text
  , col_name  text
  , col_value status_t;  -- or cast to text for any type
  , row_count_notnull int8
  )
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sch text;
   _tbl text;
   _col text;
BEGIN
   FOR _sch, _tbl, _col IN
      SELECT c.relnamespace::regnamespace, c.relname, a.attname
      FROM   pg_class     c
      JOIN   pg_attribute a ON a.attrelid = c.oid
      WHERE  c.relnamespace = ANY ('{projet, public}'::regnamespace[])  -- project?
      AND    c.relkind = 'r'           -- only regular tables
      AND    a.attname LIKE '%\_stat'  -- a_stat, b_stat
      AND    a.attnum > 0              -- only user columns (redundant here)
      AND    NOT a.attisdropped        -- exclude dropped columns
   LOOP
      RETURN QUERY EXECUTE format(
         'SELECT $1, $2, $3, %1$I, count(%1$I)
          FROM   %2$I.%3$I
          GROUP  BY 4'
       , _col, _sch, _tbl)
      USING _sch, _tbl, _col;
   END LOOP;
END
$func$;

Call:

SELECT * FROM foo();

You cannot not SELECT without target (SELECT * FROM result;), neither in a DO command nor in a PL/pgSQL function. (You could in a plain SQL function, but you have no loop there.) I return results with RETURN QUERY EXECUTE.

Pass values to EXECUTE with the USING clause.

Schema-qualify table names in dynamic SQL. Else, the name might resolve to a different schema by accident (or malicious intent).

Does not include the temporary schema like your attempt (even if you were not aware of it). You could add it using pg_my_temp_schema() if you want, but you probably did not want it anyway. See:

Related:

More examples with dynamic SQL in PL/pgSQL here on SO.

Upvotes: 1

Related Questions