Re: dynamic table names

John Smith-54 wrote
> guys,
>
> have to use legacy 8.1.
>
> i have 100,000 tables in a schema that need to be queried (optimizing this
> by combining them into one will have to wait).
>
> so my query goes like so:
>
>> execute 'select * from ' || tabname::regclass || ' where firstname =
> "john"' into e;
>
> but i am getting an error:
>
>> ERROR: syntax error at or near "'select * from '" at character 9

First, your treatment of the constant "john" is problematic since
double-quotes are not going to work. You will need to use
quote_literal(...) just like the example in the documentation shows. And
you should probably use "quote_ident(...)" as well. In fact depending on
the name of your table you may have to.

Second at first glance I do not see where you have a syntax in your e-mail
but that does not mean your code is correct since you have not provided it.

The only unusual thing is the casting to regclass of "tabname" and that too
falls into the second problem that you haven't actually defined where
"tabname" comes from or what values it could take on.

I'm also curious if you realize that "EXECUTE" used this way has to be done
within a function. I assume you do because of the presence of the "tabname"
variable in your example but again you provide no actually executable code
so there is no way we know for sure.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/dynamic-table-names-tp5764134p5764139.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.