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

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