Thread: 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