From: Andreas Haumer <andreas@xss.co.at>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Trigger functions with dynamic SQL
Date: Sat, 24 Jul 2004 17:46:33 +0200
Message-ID: <41028459.6030002@xss.co.at> (raw)
In-Reply-To: <4370.1090681307@sss.pgh.pa.us>
References: <410272AD.9010100@xss.co.at>
<4370.1090681307@sss.pgh.pa.us>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi!
Many thanks for your reply!
Tom Lane wrote:
> Andreas Haumer <andreas@xss.co.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@xss.co.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-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas@xss.co.at, tgl@sss.pgh.pa.us
Subject: Re: Trigger functions with dynamic SQL
In-Reply-To: <41028459.6030002@xss.co.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox