2

I want to achieve a sql query to make a list of all user defined trigger functions. Like the list from pg_admin below.

enter image description here

When I call:

SELECT proname, prorettype,proowner from pg_proc WHERE  prorettype = 2279;

I also get predefined functions

enter image description here

I hope you can help me

0

1 Answer 1

4

If you want to select all functions and leave out the ones created by the system, you can add a JOIN with the table pg_user and select the functions that were not created by postgres:

SELECT p.oid,proname,prosrc,u.usename
FROM  pg_proc p  
JOIN  pg_user u ON u.usesysid = p.proowner  
WHERE usename <> 'postgres' AND prorettype = 2279;

Alternatively, you could also specify a user (or many) to the where clause within a IN expression:

WHERE usename IN ('user1','user2')

EDIT: see comments. If the sysadmin created functions using the user postgres I would suggest to change the owner to another user.

ALTER TABLE public.mytable OWNER TO myuser;

If for some reason ALTER TABLE is not possible, try selecting by language (sql, plpgsql, etc) or just filter out the functions containing the language internal:

SELECT p.oid,proname,prosrc,u.usename
FROM  pg_proc p  
JOIN  pg_user u ON u.usesysid = p.proowner  
WHERE prolang = 13436 AND prorettype = 2279;

Or maybe ..

SELECT p.oid,proname,prosrc,u.usename
FROM  pg_proc p  
JOIN  pg_user u ON u.usesysid = p.proowner  
WHERE prolang <> 12 AND prorettype = 2279; --12=internal

See table pg_language to get the oid of your function's language, e.g. of my instance:

SELECT oid, lanname FROM pg_language;

  oid  | lanname  
-------+----------
    12 | internal
    13 | c
    14 | sql
 13436 | plpgsql
Sign up to request clarification or add additional context in comments.

3 Comments

That is a good answer. Lets say that some inexperienced sys_admin have made these using the postgres user. Do you have an idea on how to fix the problem then.
@PeterMølgaardPallesen mm.. that's pretty hard. I would give a shot to adding prolang = 13436 (see the edit to my answer), so that you can avoid any internal functions
Alternatively one can filter by p.oid being higher that a given number

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.