2009/8/30 Martijn van Oosterhout <kleptog@xxxxxxxxx>: > 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@xxxxxxxxx> 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----- > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
