Index or View of all UUIDs in Postgres DB? I have a Postgres database with about 35 tables of varying sizes. Every table has an auto incrementing integer primary key, as well as a column of native Postgres uuid data type recording Version 4 UUID values.

The application code logs UUIDs, but...alas...doesn't always log the object type or table. 

While we're working on cleaning that up, we sometimes just want to figure out what row a UUID refers to. 

Is there a reasonable strategy to create a view, index, or even full on table to efficiently find a row given a UUID, if every row in the database has a UUID? Or am I chasing an antipattern and should solve it at the application level?
Disclaimer: I think the whole idea doesn't really make sense. But anyway here is one way to get a uniform "view" on all UUIDs in the database.

You can create a function that goes through all columns that are defined as UUID and then returns the values for those columns. You can optionally pass a UUID value that should be searched for:

Something like: 

create or replace function find_uuids(p_id uuid default null)
  returns table(schema_name text, table_name text, column_name text, uid uuid)
as
$$
declare
  l_row record;
  l_sql text;
begin
  for l_row in select n.nspname, tb.relname, col.attname
               from pg_attribute col
                 join pg_type ty on ty.oid = col.atttypid
                 join pg_class tb on tb.oid = col.attrelid
                 join pg_namespace n on n.oid = tb.relnamespace
               where col.attnum &gt; 0
                 and not col.attisdropped
                 and ty.typname = 'uuid' 
                 and n.nspname not in ('pg_catalog', 'information_schema')
                 and n.nspname not like 'temp%' 
  loop
    l_sql := format('select %L as schema_name, 
                            %L as table_name, 
                            %L as column_name, 
                            %I as uid 
                     from %I.%I', l_row.nspname, l_row.relname, l_row.attname, l_row.attname, l_row.nspname, l_row.relname);
    if p_id is not null then 
       l_sql := l_sql || format(' where %I = %L', l_row.attname, p_id);
    end if;
    return query execute l_sql;
  end loop;
end;
$$
language plpgsql;


Then you can use:

select *
from find_uuids('19d2efcc-45c1-4371-addc-45028a054b85');


to find the tables where that UUID value appears, or 

select *
from find_uuids();


to see everything.

The above returns something like this:

schema_name | table_name | column_name | uid                                 
------------+------------+-------------+-------------------------------------
public      | uuid_test  | some_id     | 666ce0a8-11c9-11e0-b5e2-3b49c86ffc2c
public      | uuid_test  | some_id     | 7fffda0c-11c9-11e0-967c-a300aec7eb54
public      | some_table | id          | 3aa04664-863c-43e6-9890-1087db021fd8


But this is going to be really slow