List Stored Functions That Reference a Table in Postgresql

PostgreSQL: How to list all stored functions that access specific table

Perhaps is not very precise, but this query should work.

SELECT routine_schema, routine_name 
FROM information_schema.routines 
WHERE routine_definition ~ 'student' 
AND routine_definition ~ 'studentid' 
AND routine_definition ~ 'studentname';

Depending on how you wrote the procedure, can you apply more precise regex expressions. In example, if you always wrote tables and columns in this form: table.column could you use this expression:

... WHERE routine_definition ~ 'student\.studentid' 
AND routine_definition ~ 'student\.studentname'

PostgreSQL: How to list all stored functions that access specific table

The body of a function is just stored as string. There is no list of referenced objects. (That's different from views, for instance, where actual links to referenced tables are saved.)

This query for Postgres 10 or older uses the system catalog information function pg_get_functiondef() to reconstruct the CREATE FUNCTION script for relevant functions and searches for the table name with a case-insensitive regular expression:

SELECT n.nspname AS schema_name
     , p.proname AS function_name
     , pg_get_function_arguments(p.oid) AS args
     , pg_get_functiondef(p.oid) AS func_def
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  NOT p.proisagg
AND    n.nspname NOT LIKE 'pg_%'
AND    n.nspname <> 'information_schema'
AND    pg_get_functiondef(p.oid) ~* '\mbig\M';

It should do the job, but it's obviously not bullet-proof. It can fail for dynamic SQL where the table name is generated dynamically and it can return any number of false positives - especially if the table name is a common word.

Aggregate functions and all functions from system schemas are excluded.

\m and \M mark the beginning and end of a word in the regular expression.

The system catalog pg_proc changed in Postgres 11. proisagg was replaced by prokind, true stored procedures were added. You need to adapt. Related:

• How to drop all of my functions in PostgreSQL?

How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?

After some searching, I was able to find the information_schema.routines table and the information_schema.parameters tables. Using those, one can construct a query for this purpose. LEFT JOIN, instead of JOIN, is necessary to retrieve functions without parameters.

SELECT routines.routine_name, parameters.data_type, parameters.ordinal_position
FROM information_schema.routines
    LEFT JOIN information_schema.parameters ON routines.specific_name=parameters.specific_name
WHERE routines.specific_schema='my_specified_schema_name'
ORDER BY routines.routine_name, parameters.ordinal_position;

PostgreSQL list of all tables/functions referencing a column name

To find tables and views containing ColumnName:

SELECT attrelid::regclass
FROM pg_attribute
WHERE attname = 'columnname'

To find functions which mention ColumnName somewhere in their code:

SELECT oid::regprocedure, prosrc
FROM pg_proc
WHERE prosrc ~* '\yColumnName\y'

(~* is a case-insensitive regex match, \y represents a word boundary.)

How to get a list of stored procedures using a specific table in PostgreSQL?

Functions which have text 'thetable' in their body.

The query returns function name, line number and line containg 'thetable':

select *
from (
    select proname, row_number() over (partition by proname) as line, textline
    from (
        select proname, unnest(string_to_array(prosrc, chr(10))) textline
        from pg_proc p
        join pg_namespace n on n.oid = p.pronamespace
        where nspname = 'public'
        and prosrc ilike '%thetable%'
        ) lines
    ) x
    where textline ilike '%thetable%';

Functions which have any argument or return value of type associated with thetable.

For example:

create function f2(rec thetable)...
create function f1() returns setof thetable... 

This query gives name, return type and types of arguments of the functions:

with rtype as (
    select reltype 
    from pg_class
    where relname = 'thetable')
select distinct on (proname) proname, prorettype, proargtypes
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
cross join rtype
where nspname = 'public'
and (
    prorettype = reltype 
    or reltype::text = any(string_to_array(proargtypes::text, ' '))) 

Of course, you can merge the queries into one. I am using them for different purposes.

PostgreSQL: SQL script to get a list of all tables that have a particular column as foreign key

SELECT
    r.table_name
FROM information_schema.constraint_column_usage       u
INNER JOIN information_schema.referential_constraints fk
           ON u.constraint_catalog = fk.unique_constraint_catalog
               AND u.constraint_schema = fk.unique_constraint_schema
               AND u.constraint_name = fk.unique_constraint_name
INNER JOIN information_schema.key_column_usage        r
           ON r.constraint_catalog = fk.constraint_catalog
               AND r.constraint_schema = fk.constraint_schema
               AND r.constraint_name = fk.constraint_name
WHERE
    u.column_name = 'id' AND
    u.table_catalog = 'db_name' AND
    u.table_schema = 'public' AND
    u.table_name = 'table_a'

This uses the full catalog/schema/name triplet to identify a db table from all 3 information_schema views. You can drop one or two as required.

The query lists all tables that have a foreign key constraint against the column 'a' in table 'd'

PostgreSQL - find all functions and stored procedures with permissions granted to PUBLIC

I just found a solution myself. Here it is.

select routine_schema, routine_name, * 
from information_schema.role_routine_grants rrg 
where 
grantee  ilike '%public%'
order by routine_schema asc, specific_name asc, routine_name asc 

Is it possible to retrieve tables with stored procedures?

You get what you are looking for by defining a SQL function. It winds up essentially the same as your MySql.

create or replace function listar_ordenes_cat(in cat_name) 
  returns table (ordenid int) 
as $$

    select o.ordenid
      from ordenes o
     inner join detalle_ordenes d on o.ordenid = d.ordenid
     inner join productos pr      on pr.productoid = d.productoid
     inner join categorias ca     on ca.categoriaid = pr.categoriaid
     where ca.nombrecat = upper(cat_name);

$$ language sql;

But directly no you cannot get this from a stored procedure.

Postgres function passing table reference and string in RETURN QUERY

%I is for an identifier which works for creating the schema.tablename portion of the query. This portion of the query, WHERE i.webid = %I is looking for a data value not an identifier. You need to use %L. In addition as @Chris pointed out you are using the wrong variable for that value.

---

Related Topics