Thread: trigger - dynamic WHERE clause
EXECUTE 'SELECT 1 FROM ' || TG_TABLE_NAME || ' WHERE ' || whereclause || ' FOR UPDATE;'; I am generating the whereclause dynamically as the number of columns queried varies. Am I right in assuming that I cannot use EXECUTE ... USING in this scenario? -- Best Regards, Tarlika Elisabeth Schmitz
Hello 2011/5/22 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>: > EXECUTE 'SELECT 1 FROM ' || TG_TABLE_NAME || ' WHERE ' || whereclause > || ' FOR UPDATE;'; > > I am generating the whereclause dynamically as the number of columns > queried varies. > > Am I right in assuming that I cannot use EXECUTE ... USING in this > scenario? > why not? You can use it - just USING has a fixed numbers of parameters, so you should to use a arrays. Regards Pavel Stehule > -- > > Best Regards, > Tarlika Elisabeth Schmitz > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Sun, 22 May 2011 20:39:01 +0200
Pavel Stehule <pavel.stehule@gmail.com> wrote:
>Hello
>
>2011/5/22 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>:
>> EXECUTE 'SELECT 1 FROM ' || TG_TABLE_NAME || ' WHERE ' || whereclause
>> || ' FOR UPDATE;';
>>
>> I am generating the whereclause dynamically as the number of columns
>> queried varies.
>>
>> Am I right in assuming that I cannot use EXECUTE ... USING in this
>> scenario?
>>
>
>why not? You can use it - just USING has a fixed numbers of
>parameters, so you should to use a arrays.
Thank you for your responses, Pavel, and for your excellent blog pages.
Sorry, I am struggling with this a bit:
Currently, I am producing the whereclause on a subset of columns:
SELECT array_to_string (array(
SELECT newrecord.key || ' = ' || quote_literal(newrecord.value)
FROM (SELECT (each(hstore(NEW))).*) AS newrecord
WHERE newrecord.key LIKE 'id%' ), ' AND ')
INTO whereclause;
That gives me, for example:
SELECT 1 FROM test WHERE id1 = '26' AND id2 = 'name2' FOR UPDATE;
In an attempt to use EXECUTE '...' USING, I tried to execute
SELECT 1 FROM test WHERE id1 = $1 AND id2 = $2 FOR UPDATE;
I produced an array of corresponding values:
SELECT array(
SELECT newrecord.value
FROM (SELECT (each(hstore(NEW))).*) AS newrecord
WHERE newrecord.key LIKE 'id%'
) INTO av; -- text array
EXECUTE '...' USING av
==> ERROR: operator does not exist: integer = text[]
--
Best Regards,
Tarlika Elisabeth Schmitz
Hello Pavel, Thanks for taking the time to reply. On Fri, 27 May 2011 09:12:20 +0200 Pavel Stehule <pavel.stehule@gmail.com> wrote: >Hello > >2011/5/26 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>: >> On Sun, 22 May 2011 20:39:01 +0200 >> Pavel Stehule <pavel.stehule@gmail.com> wrote: >> >>>2011/5/22 Tarlika Elisabeth Schmitz >>><postgresql3@numerixtechnology.de>: >>>> EXECUTE 'SELECT 1 FROM ' || TG_TABLE_NAME || ' WHERE ' || >>>> whereclause || ' FOR UPDATE;'; >>>> >>>> I am generating the whereclause dynamically as the number of >>>> columns queried varies. >>>> >>>> Am I right in assuming that I cannot use EXECUTE ... USING in this >>>> scenario? >>>> >>> >>>why not? You can use it - just USING has a fixed numbers of >>>parameters, so you should to use a arrays. >> >> Currently, I am producing the whereclause on a subset of columns: >> >> SELECT array_to_string (array( >> SELECT newrecord.key || ' = ' || quote_literal(newrecord.value) >> FROM (SELECT (each(hstore(NEW))).*) AS newrecord >> WHERE newrecord.key LIKE 'id%' ), ' AND ') >> INTO whereclause; >> >> That gives me, for example: >> SELECT 1 FROM test WHERE id1 = '26' AND id2 = 'name2' FOR UPDATE; >> >> In an attempt to use EXECUTE '...' USING, I tried to execute >> SELECT 1 FROM test WHERE id1 = $1 AND id2 = $2 FOR UPDATE; >> >> I produced an array of corresponding values: >> [...] >> >> EXECUTE '...' USING av >> >> ==> ERROR: operator does not exist: integer = text[] >> > >I am not sure, if I understand well to your goals. I am trying to write a generic INSERT trigger, which checks whether the NEW record already exists. In the simplified example above, columns called "id*" are PK columns and they might be of different type. >The showed problem is in wrong using a array >[...] >Clause USING doesn't do a array unpacking > >you should to generate little bit different dynamic statement >EXECUTE 'SELECT .. WHERE a = $1[1] AND b = $1[2]' USING ARRAY[...] I changed that but this wasn't my only problem; typecasting was the second issue. Column "id1" is INT4 and the value obtained from NEW via each(hstore(NEW))) converted to TEXT. I can fix this by explicit typecasting: '... WHERE id1 = $1[1]::int4 ...' But there's a few things I'd be interested to understand: 1) My original version quoted all values regardless of type. I presume this worked with integers because there's some implicit typecasting going on? 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? -- Best Regards, Tarlika Elisabeth Schmitz
Hello >>[...] >>Clause USING doesn't do a array unpacking >> >>you should to generate little bit different dynamic statement >>EXECUTE 'SELECT .. WHERE a = $1[1] AND b = $1[2]' USING ARRAY[...] > > I changed that but this wasn't my only problem; typecasting was the > second issue. Column "id1" is INT4 and the value obtained from NEW via > each(hstore(NEW))) converted to TEXT. > > I can fix this by explicit typecasting: > '... WHERE id1 = $1[1]::int4 ...' > > > > But there's a few things I'd be interested to understand: > > 1) My original version quoted all values regardless of type. I presume > this worked with integers because there's some implicit typecasting > going on? > It is working usually - sometimes explicit number can help with searching a related functions. You can have a problem when function or operator is overwritten. You should to test it. > 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. Regards Pavel Stehule > > -- > > Best Regards, > Tarlika Elisabeth Schmitz > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Mon, 30 May 2011 11:02:34 +0200 Pavel Stehule <pavel.stehule@gmail.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. -- Best Regards, Tarlika Elisabeth Schmitz
2011/5/31 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>: > On Mon, 30 May 2011 11:02:34 +0200 > Pavel Stehule <pavel.stehule@gmail.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). If you need a generic triggers use different PL instead. I can not to know what requests you have to solve. But try to look on PLPerl or PLPython. Generic triggers can be developed there with less work. Regards Pavel > > -- > > Best Regards, > Tarlika Elisabeth Schmitz > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Tue, 31 May 2011 06:09:18 +0200 Pavel Stehule <pavel.stehule@gmail.com> wrote: >2011/5/31 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>: >> On Mon, 30 May 2011 11:02:34 +0200 >> Pavel Stehule <pavel.stehule@gmail.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
2011/5/31 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>: > On Tue, 31 May 2011 06:09:18 +0200 > Pavel Stehule <pavel.stehule@gmail.com> wrote: > >>2011/5/31 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>: >>> On Mon, 30 May 2011 11:02:34 +0200 >>> Pavel Stehule <pavel.stehule@gmail.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >