Is it possible to search for certain values in columns of other tables?
Example:
I have a number: 23028908
I want to search my bank for all columns that have this value.
Is this possible? How can it be done?
Is it possible to search for certain values in columns of other tables?
Example:
I have a number: 23028908
I want to search my bank for all columns that have this value.
Is this possible? How can it be done?
The following function implements what you need:
CREATE OR REPLACE FUNCTION search_columns(
needle text,
haystack_tables name[] default '{}',
haystack_schema name[] default '{public}'
)
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)
WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
AND c.table_schema=ANY(haystack_schema)
AND t.table_type='BASE TABLE'
LOOP
EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
schemaname,
tablename,
columnname,
needle
) INTO rowctid;
IF rowctid is not null THEN
RETURN NEXT;
END IF;
END LOOP;
END;
$$ language plpgsql;
To search all tables of a schema for a given value:
select * from search_columns('23028908');
To search for a value in a specific table, by dynamic name:
select * from search_columns('23028908', {tabela});
To search for a value in tables whose names come from a select
:
select * from grep_columns('23028908', array(select table_name::name from information_schema.tables where table_name like 'pessoa%'), array['public']);
More details in the original answer , which I translate here.