Re: trigger - dynamic WHERE clause

Hello Pavel,
Thanks for taking the time to reply.

On Fri, 27 May 2011 09:12:20 +0200
Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

>Hello
>
>2011/5/26 Tarlika Elisabeth Schmitz <postgresql3(at)numerixtechnology(dot)de>:
>> On Sun, 22 May 2011 20:39:01 +0200
>> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>
>>>2011/5/22 Tarlika Elisabeth Schmitz
>>><postgresql3(at)numerixtechnology(dot)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