How to get trigger function and function of PostgreSQL with SQL differently?

Question

I want to get the functions and trigger functions separately, with sql from JDBC, not command line.

update:
Sorry, it's not clear enough. I mean, trigger function is also a function. how can I tell a trigger function from normal function with sql?

As far as I know is, trigger function's return type is trigger, is it enough to use this to distinguish?

Thanks.

It's not clear what you are asking. Try to provide some more explanations and maybe an example — Konstantin V. Salikhov
– Konstantin V. Salikhov, Commented May 8, 2014 at 6:31

user330315 · Accepted Answer · 2014-05-08 06:41:16Z

9

To get the function source:

select pg_get_functiondef('public.your_trigger_function()'::regprocedure);

To get the trigger definition

select pg_get_triggerdef(oid)
from pg_trigger
where tgname = 'name_of_your_trigger';

answered May 8, 2014 at 6:41

user330315

Sign up to request clarification or add additional context in comments.

Comments

Super Kai - Kazuya Ito · Accepted Answer · 2024-01-20 03:56:28Z

1

You can get all user-defined functions with the SQL below. *The doc explains information_schema.routines:

SELECT routine_name 
FROM information_schema.routines 
WHERE routine_type IS DISTINCT FROM 'PROCEDURE'
AND specific_schema != 'pg_catalog'
AND specific_schema != 'information_schema';

Or:

SELECT routine_name 
FROM information_schema.routines 
WHERE (routine_type != 'PROCEDURE' OR routine_type IS NULL)
AND specific_schema != 'pg_catalog'
AND specific_schema != 'information_schema';

And, you can get all user-defined trigger functions with the SQL below:

SELECT routine_name 
FROM information_schema.routines 
WHERE data_type = 'trigger' AND specific_schema != 'pg_catalog';

edited Jan 20, 2024 at 3:56

answered Jan 19, 2024 at 18:30

Super Kai - Kazuya Ito

42.8k23 gold badges258 silver badges257 bronze badges

Comments

user1720827 · Accepted Answer · 2022-10-10 13:15:41Z

0

    select tgname, 
       pg_get_triggerdef(oid),
       CASE 
            WHEN split_part(pg_get_triggerdef(oid), 'FUNCTION', 2) IS NOT NULL 
                THEN pg_get_functiondef(split_part(pg_get_triggerdef(oid), 'FUNCTION', 2) ::regprocedure)   
            ELSE pg_get_functiondef(split_part(pg_get_triggerdef(oid), 'PROCEDURE', 2) ::regprocedure)  
       END
from pg_trigger
where tgname = 'test_updated';

answered Oct 10, 2022 at 13:15

user1720827

1471 gold badge4 silver badges18 bronze badges

Collectives™ on Stack Overflow

How to get trigger function and function of PostgreSQL with SQL differently?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related