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