[HACKERS] Obtaining Firing Statement clause in (pl/perlu) Trigger Function

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

[HACKERS] Obtaining Firing Statement clause in (pl/perlu) Trigger Function

Stef Telford
Hello Everyone,
    Apologies in advance, its time for another of my whacky 'can we do
this' emails ;)

    I am trying to write a function/Trigger in pl/perl (although any
other language which allows this is perfectly fine with me :) and I need to
find the firing statement. I understand that if the trigger was fired in
a long sequence, then of course, the calling statement will be the
previous trigger.

    The scenario comes from that I am trying to setup a function which
queries a remote Sybase server. I have the functionality such that this
works;
   
    select * from test_func('where userid=2313423');

    It then returns a SETOF the table in question. However, the syntax
is, to put it politely, klunky (it smells of a kludge, which, it is :).
Therefore, if I can get access to the where section of the calling
statement in the Trigger itself, i can have a syntax which reads as
follows;

    select * from test_func() where userid=2324142;

    The reason for needing the syntax inside the Trigger, is obviously
with large tables, grabbing all the tuples and then parsing them down
once obtained is, rather crazy. Some of the tables in question have at
least 3 million records. Flinging them all around only to get a few (or
most of the time one) seems rather crazy.

    Do any of the pl/XXXX languages support getting at the calling
trigger statement ?
I am not adverse to trying to 'fix' pl/perl, however, if the trigger
never even gets passed this information in the first place, well, things are
going to start getting messy, and probably spiral outside of my control
:)

    Regards and Thanks
    Stef

attachment0 (196 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] Obtaining Firing Statement clause in (pl/perlu) Trigger Function

Josh berkus
Stef,

>     I am trying to write a function/Trigger in pl/perl (although any
> other language which allows this is perfectly fine with me :) and I need to
> find the firing statement. I understand that if the trigger was fired in
> a long sequence, then of course, the calling statement will be the
> previous trigger.

Talk to David Fetter (author of DBI-Link) about this.  You're also probably
unnecessarily replicating his work.

It's not currently possible, unfortunately.   Tge real way to do this would be
through RULES.   However, RULEs currently don't give you a handle on query
substructures like where clauses (let alone join clauses).  DF and I have
talked about it, but it would take some major back-end hacking to enable
it.  :-(

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq