Re: dynamic table names - Mailing list pgsql-general

From Thomas Kellerer
Subject Re: dynamic table names
Date
Msg-id ks70ms$132$1@ger.gmane.org
Whole thread Raw
In response to dynamic table names  (John Smith <jayzee.smith@gmail.com>)
Responses Re: dynamic table names  (John Smith <jayzee.smith@gmail.com>)
List pgsql-general
John Smith wrote on 17.07.2013 22:39:
> 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
>
> ? do someone have a stored procedure for this?

"john" is a column name, not a string value. You need to use 'john'
but as that is part of another string literal you need to use two single quotes
(which is something different than one double quote)

execute 'select * from ' || tabname::regclass || ' where firstname = ''john''' into e;


pgsql-general by date:

Previous
From: David Johnston
Date:
Subject: Re: dynamic table names
Next
From: Xiang Jun Wu
Date:
Subject: About postgres scale out