Re: Queries w/ "computed" table names? (eval in Pg?)

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.

Cheers,
Steve