Skip to content

Instantly share code, notes, and snippets.

@frafra
Created October 10, 2022 16:27
Show Gist options
  • Save frafra/2893d76651223fd579cd449d68beee7d to your computer and use it in GitHub Desktop.
Save frafra/2893d76651223fd579cd449d68beee7d to your computer and use it in GitHub Desktop.
Look if any column contains such a value
create function pg_temp.look_for_value(value text) returns
table(table_fullname text, column_name text)
language plpgsql as $$
declare
r record;
found bool;
begin
for r in select * from information_schema.columns
loop
table_fullname = format('%I.%I.%I', r.table_catalog, r.table_schema, r.table_name);
column_name = r.column_name;
execute format('select true from %s where %I::text = %L limit 1',
table_fullname, column_name, value) into found;
if found then return next;
end if;
end loop;
end
$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment