On Fri, Feb 22, 2008 at 6:51 PM, Steve Atkins <[EMAIL PROTECTED]> wrote:
> > On Feb 22, 2008, at 3:40 PM, Kynn Jones wrote: > > > Hi. Suppose I have a database that contains a "meta table" that > > holds the names of other the tables in the database, keyed by human- > > readable but longish strings. I would like to write queries that > > first "compute" the names of some tables (i.e. by looking them up in > > "meta table"), and after that they execute subqueries using these > > computed table names. The following invalid SQL illustrates the > > kind of maneuver I'd like to do: > > > > SELECT x, y, z > > FROM [ SELECT table_name FROM meta_table > > WHERE human_readable_key = > > 'some veeeery long and unwieldy string' ]; > > > > The stuff in [ brackets ] is not meant to be valid SQL, but rather > > to suggest that the name of the table for the "outer" query > > corresponds to the string returned by the "inner" (bracketed) query. > > > > Some programming languages allow the run-time evaluation of a string > > representing some code in the language. One way to do what I'd like > > to do is based on this idea: I would construct the source code for > > the desired subquery as a string (including the name of the table > > obtained at run-time from meta_table), and "somehow" evaluate this > > string. This "somehow" is what I'm missing. Is there a way in > > PostgreSQL to evaluate a string as SQL? > > > > You can do it from within pl/pgsql - see > http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > > I don't think there's any way to do it from plain sql, but you could > probably create a small pl/pgsql wrapper function to do it. Yep, that's just what I was looking for. Thanks to you, and to Jeff also! Kynn
