Search Postgresql Archives

Re: trigger - dynamic WHERE clause

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux