Re: 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(at)blighty(dot)com> 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