Re: trigger - dynamic WHERE clause

2011/5/31 Tarlika Elisabeth Schmitz <postgresql3(at)numerixtechnology(dot)de>:
> 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?
>

maybe both - when you know Perl or Python

>
> 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?
>

little bit maybe 20% faster

Pavel

>
>
> --
>
> Best Regards,
> Tarlika Elisabeth Schmitz
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>