Thread: Queries w/ "computed" table names? (eval in Pg?)
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?
TIA!
kynn
On Fri, 2008-02-22 at 18:40 -0500, 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
You can create a PL/pgSQL function and use "EXECUTE".
Depending on how you want to use it, you may need to make it a set-
returning function (a.k.a. table function).
Regards,
Jeff Davis
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
On Fri, Feb 22, 2008 at 6:51 PM, Steve Atkins <steve@blighty.com> wrote:
You can do it from within pl/pgsql - see http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
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?
>
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