pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedusing pg_tables and tablename in queries
10+ messages / 6 participants
[nested] [flat]
* using pg_tables and tablename in queries
@ 2005-10-04 16:30 solarsail <solarsail@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: solarsail @ 2005-10-04 16:30 UTC (permalink / raw)
To: pgsql-sql
I have a large number of tables with a common naming convention
mytable001, mytable002, mytable003 ... mytable00n
I would like to do a query across all of the tables, however I do not know
all of the tables before hand, and I do not want to ( cant ) manually
generate a query like
select * from mytable001, mytable002, mytable003
I have a query that returns the names of the tables I want to query:
select tablename from pg_tables where tablename like 'mytable%'
I have successfully done this query before. I remember it used a odd syntax;
I it was something like
select * from ( select tablename from pg_tables where tablename like
'mytable%' )
However the above query returns just a listing of tables, I need to use its
output in the FROM and treat the input as a table name.
( I've tried creating a Table Function that returns the above set and tried
to use that in a select clause, but I cant get it to work. )
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: using pg_tables and tablename in queries
@ 2005-10-04 17:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: solarsail <solarsail@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Tom Lane @ 2005-10-04 17:01 UTC (permalink / raw)
To: solarsail <solarsail@gmail.com>; +Cc: pgsql-sql
solarsail <solarsail@gmail.com> writes:
> I have a large number of tables with a common naming convention
> mytable001, mytable002, mytable003 ... mytable00n
> I would like to do a query across all of the tables, however I do not know
> all of the tables before hand, and I do not want to ( cant ) manually
> generate a query like
> select * from mytable001, mytable002, mytable003
> I have a query that returns the names of the tables I want to query:
> select tablename from pg_tables where tablename like 'mytable%'
This looks to me like a situation in which you should rethink your
data design. Those tables should all get merged into one big table,
adding one extra column that reflects what you had been using to
segregate the data into different tables.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: using pg_tables and tablename in queries
@ 2005-10-04 18:40 solarsail <solarsail@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 10+ messages in thread
From: solarsail @ 2005-10-04 18:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
The current behavior is by design.
We use the table as a logging repository. It can get very large 250 000
records. Because of the large number of records that we have in the table we
found it was much faster to perform inserts on a smaller table. Our current
system rolls the tables over every 12 hours or so, creating a new table with
the following behavior:
CREATE TABLE mytable_temp {...}
ALTER TABLE mytable RENAME TO mytable_back_datetime;
ALTER TABLE mytable_temp RENAME TO mytable;
I want to join the mytable_back_datetime tables together in order to perform
queries against my huge set of data to generate some reports. I'm probably
going to create a temporary table with a few indexes to make the reports run
faster... however I need to join the tables all together first.
On 10/4/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> solarsail <solarsail@gmail.com> writes:
> > I have a large number of tables with a common naming convention
>
> > mytable001, mytable002, mytable003 ... mytable00n
>
> > I would like to do a query across all of the tables, however I do not
> know
> > all of the tables before hand, and I do not want to ( cant ) manually
> > generate a query like
>
> > select * from mytable001, mytable002, mytable003
>
> > I have a query that returns the names of the tables I want to query:
>
> > select tablename from pg_tables where tablename like 'mytable%'
>
> This looks to me like a situation in which you should rethink your
> data design. Those tables should all get merged into one big table,
> adding one extra column that reflects what you had been using to
> segregate the data into different tables.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: using pg_tables and tablename in queries
@ 2005-10-04 19:23 Yasir Malik <ymalik@cs.stevens.edu>
parent: solarsail <solarsail@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Yasir Malik @ 2005-10-04 19:23 UTC (permalink / raw)
To: pgsql-sql
> The current behavior is by design.
>
> We use the table as a logging repository. It can get very large 250 000
> records. Because of the large number of records that we have in the table we
> found it was much faster to perform inserts on a smaller table. Our current
> system rolls the tables over every 12 hours or so, creating a new table with
> the following behavior:
>
> CREATE TABLE mytable_temp {...}
>
> ALTER TABLE mytable RENAME TO mytable_back_datetime;
> ALTER TABLE mytable_temp RENAME TO mytable;
>
> I want to join the mytable_back_datetime tables together in order to perform
> queries against my huge set of data to generate some reports. I'm probably
> going to create a temporary table with a few indexes to make the reports run
> faster... however I need to join the tables all together first.
>
I would create a function that creates a string with a query that includes
all the tables you need, and call execute on the string. You would loop
through the all tables from pg_tables and keep on appending the table name
you need.
Regards,
Yasir
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: using pg_tables and tablename in queries
@ 2005-10-04 19:45 Jim Buttafuoco <jim@contactbda.com>
parent: solarsail <solarsail@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Jim Buttafuoco @ 2005-10-04 19:45 UTC (permalink / raw)
To: solarsail <solarsail@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
do you mean UNION ALL instead of JOIN, if you mean UNION ALL , I would go with a set returning function passing it
the necessary WHERE clause to be applied to all of your tables. You might be able to wrap the whole thing into a view
---------- Original Message -----------
From: solarsail <solarsail@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Sent: Tue, 4 Oct 2005 14:40:54 -0400
Subject: Re: [SQL] using pg_tables and tablename in queries
> The current behavior is by design.
>
> We use the table as a logging repository. It can get very large 250 000
> records. Because of the large number of records that we have in the table we
> found it was much faster to perform inserts on a smaller table. Our current
> system rolls the tables over every 12 hours or so, creating a new table with
> the following behavior:
>
> CREATE TABLE mytable_temp {...}
>
> ALTER TABLE mytable RENAME TO mytable_back_datetime;
> ALTER TABLE mytable_temp RENAME TO mytable;
>
> I want to join the mytable_back_datetime tables together in order to perform
> queries against my huge set of data to generate some reports. I'm probably
> going to create a temporary table with a few indexes to make the reports run
> faster... however I need to join the tables all together first.
>
> On 10/4/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > solarsail <solarsail@gmail.com> writes:
> > > I have a large number of tables with a common naming convention
> >
> > > mytable001, mytable002, mytable003 ... mytable00n
> >
> > > I would like to do a query across all of the tables, however I do not
> > know
> > > all of the tables before hand, and I do not want to ( cant ) manually
> > > generate a query like
> >
> > > select * from mytable001, mytable002, mytable003
> >
> > > I have a query that returns the names of the tables I want to query:
> >
> > > select tablename from pg_tables where tablename like 'mytable%'
> >
> > This looks to me like a situation in which you should rethink your
> > data design. Those tables should all get merged into one big table,
> > adding one extra column that reflects what you had been using to
> > segregate the data into different tables.
> >
> > regards, tom lane
> >
------- End of Original Message -------
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: using pg_tables and tablename in queries
@ 2005-10-04 19:50 solarsail <solarsail@gmail.com>
parent: Yasir Malik <ymalik@cs.stevens.edu>
0 siblings, 1 reply; 10+ messages in thread
From: solarsail @ 2005-10-04 19:50 UTC (permalink / raw)
To: Yasir Malik <ymalik@cs.stevens.edu>; +Cc: pgsql-sql
I managed to make this work as sub query before... I wish I had
written it down somewhere...
Regarding the creation of a function. I do have a function that
almost does that. I'm having a hard time getting it to return a set
of records from the EXECUTE command ( more than one row returned by
the select * ...).
If I generate a temporary table instead of returning the results how
long will that table exist for? Excuse the OOP terminology but would
it be correct to create a 'Singleton' to access the temporary table,
where if it exists and is less than 30 minutes old use that one,
otherwise drop the table and recreate it?
Thanks
-- sample function..
CREATE OR REPLACE FUNCTION testfunc_jointables()
RETURNS SETOF record AS
$BODY$
DECLARE
query TEXT;
BEGIN
query := 'auditrecord';
FOR
atablename IN select * from pg_tables where tablename like 'mytable_%'
LOOP
query := query || ', ' || quote_ident(atablename.tablename);
END LOOP;
EXECUTE ' SELECT * from ' || query;
END;
On 10/4/05, Yasir Malik <ymalik@cs.stevens.edu> wrote:
> > The current behavior is by design.
> >
> > We use the table as a logging repository. It can get very large 250 000
> > records. Because of the large number of records that we have in the table we
> > found it was much faster to perform inserts on a smaller table. Our current
> > system rolls the tables over every 12 hours or so, creating a new table with
> > the following behavior:
> >
> > CREATE TABLE mytable_temp {...}
> >
> > ALTER TABLE mytable RENAME TO mytable_back_datetime;
> > ALTER TABLE mytable_temp RENAME TO mytable;
> >
> > I want to join the mytable_back_datetime tables together in order to perform
> > queries against my huge set of data to generate some reports. I'm probably
> > going to create a temporary table with a few indexes to make the reports run
> > faster... however I need to join the tables all together first.
> >
>
> I would create a function that creates a string with a query that includes
> all the tables you need, and call execute on the string. You would loop
> through the all tables from pg_tables and keep on appending the table name
> you need.
>
> Regards,
> Yasir
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: using pg_tables and tablename in queries
@ 2005-10-04 22:33 Gregory S. Williamson <gsw@globexplorer.com>
0 siblings, 0 replies; 10+ messages in thread
From: Gregory S. Williamson @ 2005-10-04 22:33 UTC (permalink / raw)
To: solarsail <solarsail@gmail.com>; Yasir Malik <ymalik@cs.stevens.edu>; +Cc: pgsql-sql
Yasir --
You wrote:
<snip>
>If I generate a temporary table instead of returning the results how
>long will that table exist for? Excuse the OOP terminology but would
>it be correct to create a 'Singleton' to access the temporary table,
>where if it exists and is less than 30 minutes old use that one,
>otherwise drop the table and recreate it?
In 8.0:
"Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below).
Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well."
So a temp table would persist as long as the originating session, but only that session could see it.
Not sure if this helps or not ...
Greg Williamson
DBA
GlobeXplorer LLC
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: using pg_tables and tablename in queries
@ 2005-10-05 01:01 Jim Buttafuoco <jim@contactbda.com>
parent: solarsail <solarsail@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Jim Buttafuoco @ 2005-10-05 01:01 UTC (permalink / raw)
To: solarsail <solarsail@gmail.com>; Yasir Malik <ymalik@cs.stevens.edu>; +Cc: pgsql-sql
again, do you really want to join the tables or do a UNION ALL. From one of your posts you said the table were the
same.
you need to do something like
select * from table_001
union all
select * from table_002
...
select * from table_999
I would do this in a set returning function looping of an EXECUTE. If you need help, post the schema for a couple of
your tables and I will help with the function
Jim
---------- Original Message -----------
From: solarsail <solarsail@gmail.com>
To: Yasir Malik <ymalik@cs.stevens.edu>
Cc: PostgreSQL <pgsql-sql@postgresql.org>
Sent: Tue, 4 Oct 2005 15:50:39 -0400
Subject: Re: [SQL] using pg_tables and tablename in queries
> I managed to make this work as sub query before... I wish I had
> written it down somewhere...
>
> Regarding the creation of a function. I do have a function that
> almost does that. I'm having a hard time getting it to return a set
> of records from the EXECUTE command ( more than one row returned by
> the select * ...).
>
> If I generate a temporary table instead of returning the results how
> long will that table exist for? Excuse the OOP terminology but would
> it be correct to create a 'Singleton' to access the temporary table,
> where if it exists and is less than 30 minutes old use that one,
> otherwise drop the table and recreate it?
>
> Thanks
>
> -- sample function..
>
> CREATE OR REPLACE FUNCTION testfunc_jointables()
> RETURNS SETOF record AS
> $BODY$
> DECLARE
> query TEXT;
> BEGIN
> query := 'auditrecord';
>
> FOR
> atablename IN select * from pg_tables where tablename like 'mytable_%'
> LOOP
>
> query := query || ', ' || quote_ident(atablename.tablename);
>
> END LOOP;
>
> EXECUTE ' SELECT * from ' || query;
>
> END;
>
> On 10/4/05, Yasir Malik <ymalik@cs.stevens.edu> wrote:
> > > The current behavior is by design.
> > >
> > > We use the table as a logging repository. It can get very large 250 000
> > > records. Because of the large number of records that we have in the table we
> > > found it was much faster to perform inserts on a smaller table. Our current
> > > system rolls the tables over every 12 hours or so, creating a new table with
> > > the following behavior:
> > >
> > > CREATE TABLE mytable_temp {...}
> > >
> > > ALTER TABLE mytable RENAME TO mytable_back_datetime;
> > > ALTER TABLE mytable_temp RENAME TO mytable;
> > >
> > > I want to join the mytable_back_datetime tables together in order to perform
> > > queries against my huge set of data to generate some reports. I'm probably
> > > going to create a temporary table with a few indexes to make the reports run
> > > faster... however I need to join the tables all together first.
> > >
> >
> > I would create a function that creates a string with a query that includes
> > all the tables you need, and call execute on the string. You would loop
> > through the all tables from pg_tables and keep on appending the table name
> > you need.
> >
> > Regards,
> > Yasir
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
------- End of Original Message -------
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: using pg_tables and tablename in queries
@ 2005-10-05 15:24 codeWarrior <gpatnude@hotmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 10+ messages in thread
From: codeWarrior @ 2005-10-05 15:24 UTC (permalink / raw)
To: pgsql-sql
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:9420.1128445291@sss.pgh.pa.us...
> solarsail <solarsail@gmail.com> writes:
>> I have a large number of tables with a common naming convention
>
>> mytable001, mytable002, mytable003 ... mytable00n
>
>> I would like to do a query across all of the tables, however I do not
>> know
>> all of the tables before hand, and I do not want to ( cant ) manually
>> generate a query like
>
>> select * from mytable001, mytable002, mytable003
>
>> I have a query that returns the names of the tables I want to query:
>
>> select tablename from pg_tables where tablename like 'mytable%'
>
> This looks to me like a situation in which you should rethink your
> data design. Those tables should all get merged into one big table,
> adding one extra column that reflects what you had been using to
> segregate the data into different tables.
>
> regards, tom lane
Sounds like a classic opportunity to use the postgreSQL inheritance model to
me....
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: using pg_tables and tablename in queries
@ 2005-10-07 19:54 solarsail <solarsail@gmail.com>
parent: codeWarrior <gpatnude@hotmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: solarsail @ 2005-10-07 19:54 UTC (permalink / raw)
To: pgsql-sql
Thanks for the help everyone. I got everything working.
The query I was orignally looking for was something like this:
SELECT * from mytable WHERE pg_tables.tablename IN
( select tablename from pg_tables where tablename like 'mytable _%' )
It turns out that isn't what I really wanted.
I now have a nice stored procedure, with a clean interface. Thanks
for all the help!
On 10/5/05, codeWarrior <gpatnude@hotmail.com> wrote:
>
> "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
> news:9420.1128445291@sss.pgh.pa.us...
> > solarsail <solarsail@gmail.com> writes:
> >> I have a large number of tables with a common naming convention
> >
> >> mytable001, mytable002, mytable003 ... mytable00n
> >
> >> I would like to do a query across all of the tables, however I do not
> >> know
> >> all of the tables before hand, and I do not want to ( cant ) manually
> >> generate a query like
> >
> >> select * from mytable001, mytable002, mytable003
> >
> >> I have a query that returns the names of the tables I want to query:
> >
> >> select tablename from pg_tables where tablename like 'mytable%'
> >
> > This looks to me like a situation in which you should rethink your
> > data design. Those tables should all get merged into one big table,
> > adding one extra column that reflects what you had been using to
> > segregate the data into different tables.
> >
> > regards, tom lane
>
> Sounds like a classic opportunity to use the postgreSQL inheritance model to
> me....
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2005-10-07 19:54 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-04 16:30 using pg_tables and tablename in queries solarsail <solarsail@gmail.com>
2005-10-04 17:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-04 18:40 ` solarsail <solarsail@gmail.com>
2005-10-04 19:23 ` Yasir Malik <ymalik@cs.stevens.edu>
2005-10-04 19:50 ` solarsail <solarsail@gmail.com>
2005-10-05 01:01 ` Jim Buttafuoco <jim@contactbda.com>
2005-10-04 19:45 ` Jim Buttafuoco <jim@contactbda.com>
2005-10-05 15:24 ` codeWarrior <gpatnude@hotmail.com>
2005-10-07 19:54 ` solarsail <solarsail@gmail.com>
2005-10-04 22:33 Re: using pg_tables and tablename in queries Gregory S. Williamson <gsw@globexplorer.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox