Re: Trouble using TG_TABLE_NAME in BEFORE INSERT OR UPDATE trigger

2009/8/30 Martijn van Oosterhout <kleptog(at)svana(dot)org>:
> On Sun, Aug 30, 2009 at 03:22:16PM +0200, Pavel Stehule wrote:
>> Hello
>>
>> you cannot use variable as table or column name in direct query.
>>
>> look on execute statemen:
>> http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>>
>> or don't use generic triggers.
>
> You know, it would be really nice to have a way of dealing with this. I
> was thinking, just like we have the OPERATOR() keyword for tokens that
> wouldn't normally be considered operators, we could use TABLE() to
> introduce table name where they wouldn't normally be recognised. Like:
>
> SELECT a FROM TABLE($1);
>
> TABLE is a reserved word, so there doesn't appear to be a backward
> compatability problem. The argument would have to be a text constant,
> or a bind parameter. Sure, it would disable query caching, but the
> benefits in clarity seem worth it.
>
> Have a nice day,

The core of this topic is plan and syntax query analysis - different
tables has different columns and indexes. So plan should be different
and some queries should be correct (or not) for different tables.

Theoretically we can define some "operator" table for plpgsql. This
operator ensure so query will be dynamic. But I am against. It add to
plpgsql some new alchemy. Now the plpgsql block schema is very clear
and protect young programmers against some basic error. Dynamic
queries should be slower - or faster. It is best when any developer
have to understand to differences between query and dynamic query
early - lot of magical things in plpgsql will be clean.

I disliked dynamic sql too. But with USING clause the working with it
is comfortable and clean:

sample:
EXECUTE 'SELECT * FROM ' || tabname::regclass || WHERE col=$1' USING var;

older variant
EXECUTE 'SELECT * FROM ' || quote_iden(tabname) || 'WHERE col='||
quote_literal(var) ...

regards
Pavel Stehule

> --
> Martijn van Oosterhout   <kleptog(at)svana(dot)org>   http://svana.org/kleptog/
>> Please line up in a tree and maintain the heap invariant while
>> boarding. Thank you for flying nlogn airlines.
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iD8DBQFKmoChIB7bNG8LQkwRAhd1AJ9HevaBdodmpJ1sKSOjyr+70d25cACfRRpl
> KDDTg2K8xopGGBIh8A1pBtg=
> =/dQN
> -----END PGP SIGNATURE-----
>
>