Search Postgresql Archives

Re: dynamic table names

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux