Postgres Pro
Downloads
Home   >   mailing lists

Thread: dynamic table names

dynamic table names

From
John Smith
Date:
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?

http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
http://www.postgresql.org/message-id/aa44f4a30510071254p46064ca0vff639cd492b22c0b@mail.gmail.com
http://stackoverflow.com/questions/5772699/postgresql-dynamic-table-access
http://www.sql-workbench.net/

thanks, jzs

Re: dynamic table names

From
Rob Sargent
Date:
On 07/17/2013 02:39 PM, John Smith 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
>
> ? do someone have a stored procedure for this?
>
> http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
> http://www.postgresql.org/message-id/aa44f4a30510071254p46064ca0vff639cd492b22c0b@mail.gmail.com
> http://stackoverflow.com/questions/5772699/postgresql-dynamic-table-access
> http://www.sql-workbench.net/
>
> thanks, jzs
>
Drop the outer single quotes.

Re: dynamic table names

From
Rob Sargent
Date:
On 07/17/2013 02:39 PM, John Smith 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
>
> ? do someone have a stored procedure for this?
>
> http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
> http://www.postgresql.org/message-id/aa44f4a30510071254p46064ca0vff639cd492b22c0b@mail.gmail.com
> http://stackoverflow.com/questions/5772699/postgresql-dynamic-table-access
> http://www.sql-workbench.net/
>
> thanks, jzs
>

Sorry, disregard prev post

Re: dynamic table names

From
David Johnston
Date:
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.

Re: dynamic table names

From
Thomas Kellerer
Date:
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;

Re: dynamic table names

From
John Smith
Date:
david, you're right. i didn't realize it had to be executed inside a function so now i'm trying this:

-- create function
create or replace function get_tables(sname varchar) returns record as $$
    select tablename from pg_tables where schemaname = $1;
$$ language 'plpgsql';

-- query tables where column 'fname' = 'john'
select * from get_tables('myschema') where fname = 'john';

any help? wishing for a "select * from *..." :)

thanks, jzs

Re: dynamic table names

From
David Johnston
Date:
John Smith-54 wrote
> any help?

Sorry.

Its hard enough teaching people via e-mail let alone teaching them on an
unsupported version of PostgreSQL that has reduced functionality with
respect to function writing compared to the more recent versions.

You are going to need to some kind of "FOR" loop to iterate over each table
you want to query and then use "EXECUTE" to actually perform the query using
the syntax you referred to earlier.

The more complete the examples of your work-in-progress you post the more
help you are likely to get.  Basically read the documentation and try things
and if you get stumped post a specific question about what is stumping you
(with as much detail as possible; but take time to organize and comment it)
to get past the block.

David J.




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

Re: dynamic table names

From
Alban Hertroys
Date:
On Jul 17, 2013, at 22:39, John Smith <jayzee.smith@gmail.com> wrote:

> so my query goes like so:
>
> > execute 'select * from ' || tabname::regclass || ' where firstname = "john"' into e;

Are those quotes around 'john' double-quotes (for identifiers) or double single-quotes (for literals)?

They look like double-quotes to me, so unless your table has a column named "john" (and lowercase at that) that would
fail.It's probably not what you meant anyway. 

Does this fare any better?
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 you always get that error or do you only get it with certain table names? If so, which ones?

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: dynamic table names

From
Merlin Moncure
Date:
On Wed, Jul 17, 2013 at 3:39 PM, John Smith <jayzee.smith@gmail.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