2011/5/31 Tarlika Elisabeth Schmitz <postgresql3@xxxxxxxxxxxxxxxxxxxx>: > On Tue, 31 May 2011 06:09:18 +0200 > Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote: > >>2011/5/31 Tarlika Elisabeth Schmitz <postgresql3@xxxxxxxxxxxxxxxxxxxx>: >>> On Mon, 30 May 2011 11:02:34 +0200 >>> Pavel Stehule <pavel.stehule@xxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
