How do you find specific column name in function/sequences

Question

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,
       t.table_name
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.

Erwin Brandstetter · Accepted Answer · 2024-07-18T16:06:36.943

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;

See:

Get column names and data types of a query, table or view

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.

Typically, 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.
But Postgres 14 added the "SQL-standard" syntax variant which is treated differently: only a parse tree is stored. See:

What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

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.

"Standard-SQL" functions, or functions with language "C" referencing libraries cannot be found at all this way. Dynamic SQL cannot be searched reliably.

How do you find specific column name in function/sequences

1 Answers1