Re: Trigger functions with dynamic SQL

Andreas Haumer <andreas(at)xss(dot)co(dot)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