Re: Trigger functions with dynamic SQL

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi!

Many thanks for your reply!

Tom Lane wrote:
> Andreas Haumer <andreas(at)xss(dot)co(dot)at> writes:
>
>>It seems I would have to use EXECUTE on dynamically constructed
>>PL/PGSQL statements in order to have my trigger function recognize
>>the parameters given to the trigger in TG_ARGV[]
>
>
> Yup, that's exactly right. plpgsql isn't designed for this; it's

Ok, that's what I thought. Thanks for confirmation.

> designed for situations where it can pre-plan and cache plans for
> queries, and dynamically-inserted table and column names would just
> break that completely. So you have to fall back to the mechanisms
> for fully general constructed-on-the-fly queries, which work but
> are a bit painful to use.
>
"a bit painful" is the understatement of the year! :-)

I just can't figure out where and how many quotation marks
I have to place in my function. Some examples would be helpful
(the examples in the manual are quite simple and always end
where it begins to become interesting for me...)

Also: what is the performance impact of using EXECUTE and
constructed-on-the-fly queries?

> You might want to look at pltcl instead, which is much friendlier
> to dynamically generated queries (since that's the only way it
> does things). Of course, if you've never used Tcl there'll be
> a bit of a learning curve :-(
>
My experience with Tcl is almost zero. :-(

What about writing trigger functions in C? It should be
quite easy to dynamically create the SQL statements needed
here and it seems I can access the trigger arguments through
the tg_trigger->tgargs array. Would this be a better way to
go? (faster code, but perhaps painful to write as well, too)
I found some examples in the PostgreSQL sources under .../contrib/spi/
I think I will have to take a deeper look at that...

- - andreas

- --
Andreas Haumer | mailto:andreas(at)xss(dot)co(dot)at
*x Software + Systeme | http://www.xss.co.at/
Karmarschgasse 51/2/20 | Tel: +43-1-6060114-0
A-1100 Vienna, Austria | Fax: +43-1-6060114-71
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFBAoRMxJmyeGcXPhERAg/OAJ9Mg5Ecp1urDhJAnCQ+k8A9N7sv+QCfe0w9
Cdlbkwt0QITR2bU+lIO0TtU=
=5tJf
-----END PGP SIGNATURE-----