Re: trigger - dynamic WHERE clause

On Tue, 31 May 2011 06:09:18 +0200
Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

>2011/5/31 Tarlika Elisabeth Schmitz <postgresql3(at)numerixtechnology(dot)de>:
>> On Mon, 30 May 2011 11:02:34 +0200
>> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>
>>>> 2) I took from your blog entry
>>>> (http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html)
>>>> that it is good practice to use EXECUTE USING.
>>>> Well, there's no danger of SQL injection as this particular DB runs
>>>> on an internal network. However, I am wondering whether EXECUTE
>>>> USING has a performance advantage?
>>>>
>>>
>>>You newer know where or who is attacker :)
>>>The performance is very similar now - the most slow part is
>>>generating of execution plan - not IO operations.
>>
>> I have converted my generic trigger to use EXECUTE ... USING.
>>
>> I need to convert all NEW values to a text array, retaining their
>> ordinal position.
>> avals(hstore(NEW)) doesn't seem to do that:
>>
>> NEW: (5,name5,1000,,,2)
>> avals(hstore(NEW)):  {5,name5,2,1000,NULL,NULL}
>>
>> The best I can come up with is a JOIN with
>> information_schema.columns.
>
>jup
>
>it should be relative expensive (slow).

O dear - I "only" have to import 1 Mio records. :(

>I can not to know what requests you have to solve.

It's actually quite simple: I wrote one generic insert trigger, which
- checks whether record already exists
- skips insert if it exists
- updates record instead

SELECT 1 FROM <table> WHERE pk-columns = NEW-pk-columns FOR UPDATE
IF exists
UPDATE <table> WHERE ...
return NULL
ELSE
return NEW

To assemble the where-clause, I either need a naming convention for the
PK columns or obtain them via the information_schema.

My current understanding is that if I want to address the NEW fields by
number rather than by name, I need to convert NEW to array. Obviously,
the array elements need to be in a predictable position then.

>If you need a generic triggers use different PL instead.
>But try to look on PLPerl or PLPython. Generic triggers can be
>developed there with less work.

quicker to write or quicker to execute?

Another thought I had, regardless of PL: I think I should use a cursor
for the UPDATE rather than where-clause. Would that be more efficient?

--

Best Regards,
Tarlika Elisabeth Schmitz