Forum OpenACS Development: dynamic strings in plpsql?
Posted by Dan Wickstrom on 11/30/00 03:50 PM
I recently saw this post on the pg-hackers mailing list. If it turns out to be true, it would be a nice feature which might prove to be useful in porting acs 4.0.
writes: > The problem with my code seems to be that the "given_field" variable isn't > being interpolated in the assignment statement. plpgsql is not a string-substitution language; you cannot expect the value of a variable to be used as a table or field name in a query. This is true because plpgsql precompiles queries into query plans and saves the plans for repeated execution. That's a win for speed but costs flexibility. You *can* get that sort of result in pltcl or plperl, which don't do any fancy caching. You just form the query as a string value using the usual expression evaluation rules of those languages, and that string gets submitted to the SQL parser and query engine. Less speed, more flexibility. I believe 7.1's plpgsql will have an EXECUTE <string> command that lets you get the second effect in plpgsql too. regards, tom lane
2: Response to dynamic strings in plpsql? (response to 1)
Posted by Don Baccus on 11/30/00 07:16 PM
Yes, it would, I don't know if it is in, though ... I'll try to check over the weekend. Best of both worlds - in general PL/Tcl kind of sucks for doing queries because you have to do any query caching yourself since it uses the SPI interface.
PL/pgSQL hasn't allowed dynamic SQL because it is a compiled language (into bytecode+query plans). Having a dynamic EXECUTE (which undoubtably uses the same SPI interface as used in PL/Tcl and referential integrity checking, etc) would be a great improvement.