Re: dynamic table names

On Wed, Jul 17, 2013 at 3:39 PM, John Smith <jayzee(dot)smith(at)gmail(dot)com> 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

when debugging EXECUTE, I generally stage the string first -- this
gives me the opportunity to see the whole query.

q := 'select * from ' || tabname::regclass || ' where firstname =
"john"' into e;
RAISE NOTICE q;
EXECUTE q;

then the problem is usually a lot more obvious. By the way, I'm very
sympathetic to being stuck on older versions: the company I work for
just spent some major bucks migrating to 9.2 and had I not shown up
probably would have dropped postgres completely.

merlin