Message-ID: <46CB6C0E.7070508@etelos.com>
Date: Tue, 21 Aug 2007 15:49:50 -0700
From: Jon Collette <jon@etelos.com>
User-Agent: Thunderbird 2.0.0.6 (Macintosh/20070728)
MIME-Version: 1.0
To: Scott Marlowe <scott.marlowe@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Make a SQL statement not run trigger
References: <46CB5876.80003@etelos.com>
	<dcc563d10708211507r351a46efxc758168f6032ab3f@mail.gmail.com>
In-Reply-To: <dcc563d10708211507r351a46efxc758168f6032ab3f@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I think this will work for what I need.  I have been messing around with 
this using select into

/select True as donothing into temporary table table_trigger_name;
then run statement that I want to be ignored
/
The trigger would have a select upon the table_trigger_name to determine 
if it should run or not.  I am having issues catching the exception when 
the table is not found.

This is my test function of my trigger

/Create or replace function trigger_test() returns boolean as $$
declare
    donothing boolean;
begin
    donothing := False;
    select donothing into donothing from table_trigger_name;
    return boolean;
end;
$$ LANGUAGE plpgsql;
/
Which of course will error when there is no table_trigger_name made for 
that session.  I couldn't find an exception in the exceptions list for 
table not found errors.


Scott Marlowe wrote:
> On 8/21/07, Jon Collette <jon@etelos.com> wrote:
>   
>> Is it possible to run an insert,update, or delete and have it not launch
>> a trigger like it normally would?
>>
>> For example could I set a value
>> DONOTRUN = True;
>> insert into contacts ....
>>     
>
> The closest thing to a session variable for pgsql is going to likely
> be a temp table.  you could have a temp table for each session that
> stores such things and the trigger looks in the temp table to see what
> to do.
>
>   
>> Or is there just a global variable I could set to disable triggers and
>> then reset it? And would that be a per connection variable?
>>     
>
> Yes, but that would open you up to race conditions.  If another
> session wanted the trigger to fire it would not get it.  Using a temp
> table would allow you to get around the race condition.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>