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? 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
