Find each table schema, name and column that doesn't contain a specific value in a PostgreSQL database

44 Views Asked by At
    DROP FUNCTION pcm_search_columns(text,name[],name[]);

    CREATE OR REPLACE FUNCTION pcm_search_columns(
    needle text,
    haystack_tables name[] default '{}',
    haystack_schema name[] default '{}'
    )
    RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
    AS $$
    begin
      FOR schemaname,tablename,columnname IN
      SELECT c.table_schema,c.table_name,c.column_name
      FROM information_schema.columns c
        JOIN information_schema.tables t ON
          (t.table_name=c.table_name AND t.table_schema=c.table_schema)
        JOIN information_schema.table_privileges p ON
          (t.table_name=p.table_name AND t.table_schema=p.table_schema
              AND p.privilege_type='SELECT')
        JOIN information_schema.schemata s ON
          (s.schema_name=t.table_schema)
      WHERE (c.table_name='el_div')
        AND (c.table_schema=ANY(haystack_schema) OR haystack_schema='{}')
        AND (t.table_type='BASE TABLE')
        AND (c.column_name='grp')
     LOOP
    for rowctid in
    execute ('SELECT id FROM %I.%I WHERE cast(%I as text)=%L',
       schemaname,
       tablename,
       columnname,
       needle
      )
    LOOP
      --uncomment next line to get some progress report
      --RAISE NOTICE 'hit in %.%', schemaname, tablename;
      RETURN NEXT;
      END LOOP;
     END LOOP;
    END;
    $$ language plpgsql;

    select * from pcm_search_columns('5','{}');

I made this function that finds rows in column grp in table el_div. I need an extra column showing rowvalue. I also need to be able to use it to get tables missing this value.

0

There are 0 best solutions below