Search all tables to display a row that contains a specific string/ id <p>I am fairly new to PostgreSQL. I want to search all tables that contain a string/id and display the row that holds the string/id. This is what I have so far:</p> <pre><code>`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' --AND c.column_name = "id" LOOP EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text) like %L', schemaname, tablename, columnname, needle ) INTO rowctid; IF rowctid is not null THEN RETURN NEXT; END IF; END LOOP; END; $$ language plpgsql; select * from search_columns('%3172226%','{}');` I run the code above and the tables that contain 3172226 get displayed. Let's assume I found that tablenamexxx contains the id. Then I run the following command: `select * from public.tablenamexxx where ctid='(x,x)';` </code></pre> <p>The command above displays the row from tablenamexxx</p> <p>I have two problem: 1) I want to alter my code so I don't have to type in the found tablenames (that contain 3172226). I want my code to find what tables contain the id and directly display the row that has the id.</p> <p>2)There are two tables that have the id (3172226). I want to display both the tables together. My guess is using union all. </p> <p>Any help would be appreciated! Thank you</p>