Обсуждение: dynamic table/col names in plpgsql

Is there a way to use a variable as the name of a table or column in plpgsql?

This might be a simple question, but I can't find the answer in the
docs.  http://www.postgresql.org/docs/8.2/interactive/plpgsql-declarations.html
hints that there are data types that correspond to table/column, but I
don't see them listed here
http://www.postgresql.org/docs/8.2/interactive/datatype.html ...

Also, I assume that once I figure out the right datatypes, I can
assign to them from functions like substring()?

Thanks,
Gary

On Jun 23, 2007, at 22:47 , gary jefferson wrote:

> Is there a way to use a variable as the name of a table or column
> in plpgsql?

AIUI, you need to use EXECUTE and build the query string yourself.

http://www.postgresql.org/docs/8.2/interactive/plpgsql-
statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Take a look at the quote_ident function as well:

http://www.postgresql.org/docs/8.2/interactive/functions-string.html

Hope this helps you get started.

Michael Glaesemann
grzm seespotcode net

On Jun 23, 2007, at 8:47 PM, gary jefferson wrote:

> Is there a way to use a variable as the name of a table or column
> in plpgsql?
>
> This might be a simple question, but I can't find the answer in the
> docs.  http://www.postgresql.org/docs/8.2/interactive/plpgsql-
> declarations.html
> hints that there are data types that correspond to table/column, but I
> don't see them listed here
> http://www.postgresql.org/docs/8.2/interactive/datatype.html ...
>
> Also, I assume that once I figure out the right datatypes, I can
> assign to them from functions like substring()?

You're probably looking for "execute".

http://www.postgresql.org/docs/8.2/static/plpgsql-
statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Cheers,
   Steve

Thanks Michael and Steve.

Related question: If I previously had a

IF EXISTS (select ...)

statement, and the 'select ...' part now needs to be run with EXECUTE,
how do I check for existence?  It looks like I need to do an 'INTO'
with a row variable?  I can't seem to find an example snippet of how
to accomplish this in the pg docs.  Where should I be looking?

Thanks again!
Gary


On 6/23/07, Michael Glaesemann <grzm@seespotcode.net> wrote:
>
> On Jun 23, 2007, at 22:47 , gary jefferson wrote:
>
> > Is there a way to use a variable as the name of a table or column
> > in plpgsql?
>
> AIUI, you need to use EXECUTE and build the query string yourself.
>
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-
> statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
> Take a look at the quote_ident function as well:
>
> http://www.postgresql.org/docs/8.2/interactive/functions-string.html
>
> Hope this helps you get started.
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>

ugh, I'm going down a rathole with this...  the dynamic part of the
query is the table name, and therefore, if I want to select into a row
variable, that variable's declaration needs to be dynamic, too.  That
seems kind of crazy, and I see no way to do that anyway.  Maybe I'm
going about this all wrong.

So, what is the right approach?  The problem I'm trying to solve is
that I want to key off one of the parameters passed into my function,
and then operate on either table_a or table_b, depending on that
parameter (do some select queries, possibly followed by an update
query).  The column names are the same for both tables, so all other
parts of the query should remain relatively static.  Its just the
table name I need to be dynamic with.

Thanks,
Gary


On 6/25/07, gary jefferson <garyjefferson123@gmail.com> wrote:
> Thanks Michael and Steve.
>
> Related question: If I previously had a
>
> IF EXISTS (select ...)
>
> statement, and the 'select ...' part now needs to be run with EXECUTE,
> how do I check for existence?  It looks like I need to do an 'INTO'
> with a row variable?  I can't seem to find an example snippet of how
> to accomplish this in the pg docs.  Where should I be looking?
>
> Thanks again!
> Gary