Hello Pavel, Thanks for taking the time to reply. On Fri, 27 May 2011 09:12:20 +0200 Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote: >Hello > >2011/5/26 Tarlika Elisabeth Schmitz <postgresql3@xxxxxxxxxxxxxxxxxxxx>: >> On Sun, 22 May 2011 20:39:01 +0200 >> Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote: >> >>>2011/5/22 Tarlika Elisabeth Schmitz >>><postgresql3@xxxxxxxxxxxxxxxxxxxx>: >>>> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
