I am trying to see if a specific column exists in any functions/sequences. I have found a script which can search tables, is there anything out there that would allow me to search in functions/sequences (generally anywhere ideally)?

select t.table_schema,
from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name 
                                and c.table_schema = t.table_schema
where c.column_name = 'product_id'
      and t.table_schema not in ('information_schema', 'pg_catalog')
order by t.table_schema;

The above can show me views/tables that have a product_id column.

Currently working with Postgres 12. Can't seem to find one that would list for functions/sequences.

1 Answer 1


I would use:

SELECT c.relnamespace::regnamespace::text AS schema, c.relkind, c.relname
FROM   pg_catalog.pg_attribute a
JOIN   pg_catalog.pg_class     c ON c.oid = a.attrelid
WHERE  a.attname = 'product_id'
AND    c.relnamespace <> ALL ('{information_schema,pg_catalog}'::regnamespace[])
ORDER  BY 1, 2;


This includes views (relkind 'v'), which are implemented as special tables with rewrite rules internally. (And some other kinds.)

A SEQUENCE never has a column named "product_id". Sequences are implemented as special internal table-like objects with these columns. You may be confusing terminology here?)

Functions (or procedures) are special. There are various types of routines. The manual about pg_proc.prokind:

f for a normal function, p for a procedure, a for an aggregate function, or w for a window function

And various programming languages. Stock Postgres ships with internal, c, sql, and plpgsql. You can install many more. Check with:

SELECT lanname FROM pg_language;

Each has their own way to deal with the function body, which can be stored as raw string, or as parse tree, or as reference to a library.

PL/pgSQL function (and procedure) bodies are stored as strings. The system does not store any dependencies involving the function body for those. They are parsed at call time, and contained statements are only executed when reached by control.

What's more, dynamic SQL can concatenate SQL strings including column names in any way, so it's practically impossible to identify functions that obfuscate their dynamic SQL enough.

You also need to define exactly which kind of 'product_id' qualifies. Only when it references a table column? Or argument parameters? Output fields?

Here is a very crude start that finds all routines (functions, procedures) that include the word 'product_id' in any way:

SELECT p.pronamespace::regnamespace::text AS schema, prokind, proname, oid::regprocedure AS name_with_args
FROM   pg_catalog.pg_proc  p
WHERE  prosqlbody::text ~ 'product_id'
OR     prosrc ~* '\yproduct_id\y';

Works for simple installations.

Functions with language "C" reference libraries and cannot be found at all this way. Dynamic SQL cannot be searched reliably.

  • thanks for getting back. The prosqlbody isnt a column in Postgres 12. Cant seem to find a suitable alternative. As an alternative I dumped the entire DB without data and just searched that way (bit crude) but think that will help me find anything I may have missed.
    – rdbmsNoob
    Aug 9, 2022 at 13:13
  • prosqlbody is new in Postgres 14 for SQL functions. See dba.stackexchange.com/q/300507/3684 Drop that filter in older versions. Aug 9, 2022 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.