Toad World® Forums

postgresql and trigger functions

Dear all,

postgresql has a peculiar behaviour regarding triggers. While in Oracle, for example, trigger body and trigger declaration and association to the table are linked, in PostgreSQL are decoupled. You have function generation, and then creation of trigger, obtained linking table and function.

Problem is that in TDM triggers function are mixed together with all the other non-trigger functions, and inside the table’s trigger there’s only declaration of matching table-trigger function.

This is IMHO cause of troubles.

For example, generating a database, you have to carefully manage dependencies, so that functions are generated AFTER the table (because they reference table fields) but BEFORE the trigger creation. Not really practical. Also when you manage the table you don’t have everything at hand, but you have to search through the functions the trigger function associated. Not practical again.

IMHO trigger functions should be part of the table itself, inside trigger SQL, placed before trigger declaration. Or at least have an option that allows during RE to have trigger functions together with trigger declarations, or separate.

Hi Roberto,

change order of generated objects to generate objects the way you need.

  1. Press F9 to open the DDL Script Generation dialog and on tab Detail Settings click the Edit button.

  2. **Order of Generated Objects **form opens. On tab General Move Functions below Entities.

  3. On tab Extension select cell next to Entity Triggers, press F2 and choose After CREATE TABLE Section item. Then click another cell to make the Apply button active and click the Apply button. Then click OK to close the form.

  4. Generate SQL code.

Regards,

Vaclav

I also headed the similar problem a few weeks ago but figured out I can manage this exactly by reordering items.

However, I would like to see trigger functions separated from common ones.
After that generating trigger function just before related trigger should be considered.

Message was edited by: Michal.K

Hi Vaclav,

sorry but it’s not practical. I have around 260 triggers on 200 tables, plus another 700 non-trigger functions. And my order generation of trigger functions and functions is different, and manually rearranging all this mess of functions is a big chaos. Also because I’d like to keep close table generation, trigger function generation and trigger generation. My ideal should be to have the trigger function part of table SQL, so that in table preview I can see the whole thing. As Oracle users see.

Instead, I have to open entity, see the SQL of the table creation, then separately search for function, open it, extract SQL preview, and merge with SQL preview of table.

(if you give me an email address, and you don’t mind, I can send you some screenshots that shows you how all this is actually managed by the concurrent product I’m trying to dump in favour of TDM… and not only… there’s something interesting about naming conventions I’d like to show you)

Hi Roberto,

I would like ask you for cooperation. I would like to know if there is some fully functional mechanism how to recognize (using SQL queries) trigger and non-trigger functions in postgreSQL. We will try to look into the matter later (we have some high-priority tasks that need to be finished soon), but if you know some query, that would be helpful.

What we need to take into consideration is not only “modeling” approach, but also possibility to correctly reverse engineer such objects, set correct settings for model update and synchronization etc.

Re SQL preview: there is another possibility. Write the trigger function to After Script tab on entity properties. This way you can keep the items together and see everything on SQL Preview tab, disadvantage is that when you try to reverse engineer such item, it will appear among functions, that’s why synchronization might be a bit complicated.

Re screenshots: Thank you very much for your offer. Please send me email to modeling@quest.com

Thank you,

Vaclav

Ok Vaclav,

well noted your email, as soon as I have a little bit of time I prepare something for you :slight_smile:

About recognizing trigger & non trigger function, I’ll look at it…

Quick and dirty solution to ease the pain: add a button near function combo to open management window for the currently selected function associated to trigger???

Vaclav, trigger function is one which returns trigger data type.

Vaclav wrote:
Write the trigger function to After Script tab on entity properties

It will require to write also trigger into after script instead of defining trigger in propper way. At the end we can v all items manualy just into AFTER SCRIPT of model :wink: Suggested solution is really dirty.

As I wrote before, trigger function should be put into DDL just before related trigger. But note, that single trigger function may be used by more triggers. In that case CREATE OR REPLACE must be ALWAYS used, or some mechanism to not generate the same function twice

Hi Michal and Roberto,

thanks for your feedback.
I appreciate it. We will try to prepare solution for this request.
CR#95959.

Regards,

Vaclav

I would like ask you for cooperation. I would like to know if there is some fully
functional mechanism how to recognize (using SQL queries) trigger and
non-trigger functions in postgreSQL.

That is pretty easy.

To list all trigger functions:

select pr.*
from pg_catalog.pg_proc pr
join pg_catalog.pg_type ty on pr.prorettype = ty.oid
join pg_namespace ns on ns.oid = pr.pronamespace and ns.nspname = ‘public’
where ty.typname = ‘trigger’;

When using ty.typname <> ‘trigger’ all functions that are not a trigger function are returned

Another alternative would be to join pg_catalog.pg_trigger to pg_catalog.pg_proc to get all triggers and their corresponding trigger function.

Any function in pg_trigger that is not listed in pg_trigger.proname is not a trigger function (or not used by a trigger)

Hope this helps.