Thread: using pg_tables and tablename in queries

I have a large number of tables with a common naming convention<br /><br />       mytable001, mytable002, mytable003
...mytable00n<br /><br /> I would like to do a query across all of the tables, however I do not know all of the tables
beforehand, and I do not want to ( cant ) manually generate a query like<br /><br />      select * from mytable001,
mytable002,mytable003<br /><br /><br /> I have a query that returns the names of the tables I want to query:<br /><br
/>   select tablename from pg_tables where tablename like 'mytable%'<br /><br /><br /> I have successfully done this
querybefore.  I remember it used a odd syntax; I it was something like <br /><br />     select * from ( select
tablenamefrom pg_tables where tablename like 'mytable%' )<br /><br /><br /> However the above query returns just a
listingof tables, I need to use its output in the FROM and treat the input as a table name.<br /><br /> ( I've tried
creatinga Table Function that returns the above set and tried to use that in a select clause, but I cant get it to
work.)<br /><br /> 

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

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.

 

> 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

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 -------

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;
BEGINquery := '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
>

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,
unlessthey are referenced with schema-qualified names. Any indexes created on a temporary table are automatically
temporaryas 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

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 -------

"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
> 

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....