Обсуждение: dynamic table names
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
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
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.
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
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.
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;
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
-- 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
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.
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.
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