Thread: Get Query in Statement Level Trigger?

Get Query in Statement Level Trigger?

From
Derry Bryson
Date:
Is it possible to get the text of the query that
caused the trigger within a statement level trigger?
It looks to me like all you can get is whether it is a
an UPDATE, DELETE, or INSERT and not much in the way
of details about the what caused the trigger.

Thanks,

Derry Bryson

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: Get Query in Statement Level Trigger?

From
Michael Fuhr
Date:
On Thu, Jun 30, 2005 at 10:56:21AM -0700, Derry Bryson wrote:
>
> Is it possible to get the text of the query that
> caused the trigger within a statement level trigger?

Not as far as I know -- somebody please correct me if I'm mistaken.
Querying pg_stat_activity or calling pg_stat_get_backend_activity()
don't work because of the lag in the statistics collector.

> It looks to me like all you can get is whether it is a
> an UPDATE, DELETE, or INSERT and not much in the way
> of details about the what caused the trigger.

You can get a little more information than that, but probably not
what you're after:

http://www.postgresql.org/docs/8.0/static/plpgsql-trigger.html

What information are you looking for and why?  What problem are you
trying to solve?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: Get Query in Statement Level Trigger?

From
Derry Bryson
Date:

--- Michael Fuhr <mike@fuhr.org> wrote:

> On Thu, Jun 30, 2005 at 10:56:21AM -0700, Derry
> Bryson wrote:
> >
> > Is it possible to get the text of the query that
> > caused the trigger within a statement level
> trigger?
>
> Not as far as I know -- somebody please correct me
> if I'm mistaken.
> Querying pg_stat_activity or calling
> pg_stat_get_backend_activity()
> don't work because of the lag in the statistics
> collector.
>
> > It looks to me like all you can get is whether it
> is a
> > an UPDATE, DELETE, or INSERT and not much in the
> way
> > of details about the what caused the trigger.
>
> You can get a little more information than that, but
> probably not
> what you're after:
>
>
http://www.postgresql.org/docs/8.0/static/plpgsql-trigger.html
>
> What information are you looking for and why?  What
> problem are you
> trying to solve?
>

This is for replication.  Currently I do it on row
level, but that generates lots of updates for queries
like 'delete from xxxx' where xxxx has lots of rows
which tends to bog down the replication.  What I was
hoping to do was replicate on a query level.

Thanks,

Derry Bryson


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com