How to get value printed on Postgres

Question

I have a requirement to translate it to an SQL script.

I am using the information schema to get all the columns of a table and print their distinct count.

I was able to get the count, but not able to print the column name properly,

PFA the below code.

I have to pass the value of the "colum_lbl" to my select clause, if I do so it is giving me an group by error.

So I passed the "colum_lbl" within quotes. now all the values of the result has hardcoded 'colum_lbl' as value, I have to replace it with the original value I read from the for Loop

Any other efficient method for this requirement will be very much appreciated. Thanks in advance

do $$
DECLARE
   colum_lbl text;
BEGIN
    
   DROP TABLE IF EXISTS tmp_table;
   
   CREATE TABLE tmp_table
   (
     colnm varchar(50),

     cnt integer       
   );
   
   FOR colum_lbl IN

      SELECT distinct column_name

        FROM information_schema.columns

      WHERE table_schema = 'cva_aggr'

      AND table_name   = 'employee' AND column_name in ('empid','empnm')


   LOOP

      EXECUTE   
  
        'Insert into tmp_table

         SELECT '' || colum_lbl || '',count(distinct ' || colum_lbl || ')
         FROM   employee ';

   END LOOP;

END; $$

Answer 1

Kindly check your code again and replace CREATE TABLE tmp_table ( colnm varchar(50), with CREATE TABLE tmp_table ( column varchar(50),