CODEWITHSUNDEEP
postgresql
Mercurial
Mercurial

Reputation: 3885

Find all tables having data in a given column

I have a set of around 500 schemas and many of them have common columns. Now whenever I have a update I have to manually see all schemas having those columns and update if they have the data.

I was trying to get all the tables having those columns against number of rows for a specific column data.

Eg. Lets say I have col1 column in scehmas A, B and C. Can I get data in following format.

Col1   table  number 
1005   A      3
1005   B      4
1005   C      5
1006   A      7

Where 1005 is a row in col1. A is table. 3 is number of rows with 1005 in col1 in table A.

Kindly excuse my formatting and lack of queries because I posted this question from mobile.

Upvotes: 0

Views: 67

Answers (1)

Anuraag Veerapaneni
Anuraag Veerapaneni

Reputation: 679

Create below function and use that for the extraction of the data

DROP FUNCTION IF EXISTS fun_test (CHARACTER VARYING);

drop type if exists fun_test_out;
create type fun_test_out as(
      "schema_name"     VARCHAR(255)
     ,"table_name"      VARCHAR(255)
     ,"column_value"    VARCHAR(255)
     ,"count"           INT 

 );  

CREATE OR REPLACE FUNCTION fun_test (colname CHARACTER VARYING)
    RETURNS SETOF fun_test_out
AS
$$
declare
    r fun_test_out%rowtype;
    l_colname VARCHAR(255);
    l_cte     TEXT;
    l_insert  TEXT;
    tables    RECORD;

begin

l_colname := colname ;

DROP TABLE IF EXISTS tmp_output;
CREATE temp TABLE tmp_output 
(
      schema_name       VARCHAR(255)
     ,table_name        VARCHAR(255)
     ,column_value      VARCHAR(255)
     ,count             INT
);

DROP TABLE IF EXISTS tmp_tablename;
CREATE temp TABLE tmp_tablename 
(
      table_schema      VARCHAR(255)
     ,table_name        VARCHAR(255)
     ,column_name       VARCHAR(255)
);

l_cte := 'Insert into tmp_tablename ' ||  chr(10) ||
         'SELECT table_schema,table_name,column_name' ||  chr(10) ||
         'FROM information_schema.columns WHERE column_name = ''' || l_colname || '''' ;

EXECUTE l_cte;

FOR tables IN 
    SELECT table_schema,table_name,column_name
    FROM tmp_tablename
LOOP

l_insert = 'Insert into tmp_output ' ||  chr(10) ||
         'SELECT ''' || tables.table_schema || ''',''' || tables.table_name || ''',' || tables.column_name || ',COUNT(*)' ||  chr(10) ||
         'FROM ' || tables.table_schema || '.' || tables.table_name ||  chr(10) ||
          'group by ' || tables.column_name
        ;

EXECUTE l_insert;

END LOOP;


/******************************************************************
                    FINAL SELECT
******************************************************************/

FOR r in 

select *
 from tmp_output
loop
RETURN NEXT r;
END LOOP;

DROP TABLE IF EXISTS tmp_output;
DROP TABLE IF EXISTS tmp_tablename;

end
$$
LANGUAGE PLPGSQL;

You can call the function using below statement

Select * from fun_test('Column_name');

Upvotes: 1

Related Questions