Re: trigger - dynamic WHERE clause

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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>