From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andreas Haumer <andreas@xss.co.at>
Cc: pgsql-sql@postgresql.org
Subject: Re: Trigger functions with dynamic SQL
Date: Sat, 24 Jul 2004 12:13:19 -0400
Message-ID: <4810.1090685599@sss.pgh.pa.us> (raw)
In-Reply-To: <41028459.6030002@xss.co.at>
References: <410272AD.9010100@xss.co.at>
<4370.1090681307@sss.pgh.pa.us>
<41028459.6030002@xss.co.at>
Andreas Haumer <andreas@xss.co.at> writes:
> I just can't figure out where and how many quotation marks
> I have to place in my function.
It's messy all right. The "dollar quoting" feature in 7.5 should make
it a lot less painful, since you can stop having to double and re-double
quote marks. If you're interested in using beta-quality code, you can
have that today. An example would go something like
CREATE FUNCTION mytrigger() RETURNS trigger AS $PROC$
DECLARE
...
EXECUTE $$ SELECT ... FROM $$ || tgargv[0] || $$ WHERE col = 'key' $$;
...
END
$PROC$ LANGUAGE plpgsql;
Here I've used minimal dollar quotes ($$) for the literal constant parts
of the EXECUTE'd query, which allows me not to have to double the quote
marks that I actually want in the query text (the ones around "key").
And I used dollar quotes with a label ($PROC$) at the outermost level
to quote the entire function body, so that there's no conflict with the
embedded dollar quotes. In 7.4 the same EXECUTE command would have to
be written
EXECUTE '' SELECT ... FROM '' || tgargv[0] || '' WHERE col = ''''key'''' '';
which is already getting painful, and more complex cases get rapidly
worse. With dollar quoting you can write the constant parts of your
query the same way you normally would.
> What about writing trigger functions in C?
Seems like the hard way to me. I doubt it would be better than plpgsql,
but it's all a matter of opinion...
regards, tom lane
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: tgl@sss.pgh.pa.us, andreas@xss.co.at
Subject: Re: Trigger functions with dynamic SQL
In-Reply-To: <4810.1090685599@sss.pgh.pa.us>
* 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