I had a case where I got an table id but was not sure what table was it refering to. I known that table contained string ‘test’.
I knew that PostgreSQL has support for for selects and also supports execute statement that dynamically runs the sql from the variable.
Once I joined these two constructs, I created this anonymous block.
The query will print out all queries and found rows to the notification channel of the PostgreSQL which in my case IDE shows. Then I just used ctrl+f to identify the exact table.
do
$$
declare
val text;
row_value text;
begin
for val in (select
'select (t.*)::text from public.' || t.table_name || ' t where t.'|| t.column_name ||' = 3'
from information_schema.columns t where t.table_schema = 'public' and t.column_name = 'id' and t.udt_name in ('int4' , 'int8', '_int4', '_int8', 'int2', '_int2')
and t.column_default ilike '%nextval%'
)
loop
execute val into row_value;
if val is not null then
raise notice '%: %', val, row_value;
end if;
end loop;
end;
$$ language plpgsql;