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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
