From: | Fabrízio de Royes Mello <fabriziomello(at)gmail(dot)com> |
---|---|
To: | Gabriel <yu1nis(at)gmail(dot)com> |
Cc: | Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Firing trigger if only |
Date: | 2014-04-07 18:08:54 |
Message-ID: | CAFcNs+oe2Coun6BU0a9y3aW0JhwSS2Y1tHOXfU1o-wN+1_sEEw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, Apr 3, 2014 at 8:50 AM, Gabriel <yu1nis(at)gmail(dot)com> wrote:
> Good afternoon all.I have some problem with triggers on PostgreSQL 8.4.I
> have
> a trigger on specific table(articles) that fires on update statement:
>
> CREATE OR REPLACE FUNCTION trigger_articles_update()
> RETURNS trigger AS
> $BODY$BEGIN
> INSERT INTO
> article_change(article_id,change_type)VALUES(OLD.article_id,2);
> RETURN NULL;
> END$BODY$
> LANGUAGE 'plpgsql' VOLATILE
> COST 100;
> ALTER FUNCTION trigger_articles_update() OWNER TO postgres;
>
> I have 2 different applications that performs update on table
> articles(written in Delphi and using ZeosDB). My problem is that I want
> trigger to fire only when performing update with first application, but not
> with second.I know that triggers supposed to fire on every change on table,
> but this is a specific problem that I have.Any hint appreciated. 8)
>
>
Since 9.0 version of PostgreSQL you can set 'application_name' in
connection [1] and test it in your trigger using a query like:
regress=# SELECT application_name FROM pg_stat_activity WHERE pid =
pg_backend_pid();
application_name
------------------
psql
(1 registro)
I strongly recommend you to upgrade your 8.4 because the EOL [2] is July
2014.
But if an upgrade isn't an option for now then you can use the old
"custom_variable_classes" to set your application, i.e.:
1) Add to your postgresql.conf:
custom_variable_classes = 'foo'
foo.application_name = 'undefined'
2) Reload your PostgreSQL
3) You can use the following functions to get/set the
'foo.application_name' custom variable:
-- get
SELECT current_setting('foo.application_name');
-- set
SELECT set_config('foo.application_name', 'myapp');
4) Now you can use this functions do register the name of your application
an use it in your trigger.
Regards,
[1]
http://www.postgresql.org/docs/current/static/runtime-config-logging.html#GUC-APPLICATION-NAME
[2] http://www.postgresql.org/support/versioning/
[3] http://www.postgresql.org/docs/8.4/static/runtime-config-custom.html
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2014-04-07 18:12:09 | Re: B-Tree support function number 3 (strxfrm() optimization) |
Previous Message | Stephen Frost | 2014-04-07 17:58:35 | Re: B-Tree support function number 3 (strxfrm() optimization) |