X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 27BC6D9B69 for ; Tue, 4 Oct 2005 13:30:58 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61987-07 for ; Tue, 4 Oct 2005 16:30:56 +0000 (GMT) Received: from qproxy.gmail.com (qproxy.gmail.com [72.14.204.197]) by svr1.postgresql.org (Postfix) with ESMTP id 1AE49D7AAB for ; Tue, 4 Oct 2005 13:30:55 -0300 (ADT) Received: by qproxy.gmail.com with SMTP id e12so763420qbe for ; Tue, 04 Oct 2005 09:30:55 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type; b=Fo3H/aMWSD3JFv+BM+mawaGn8BRZxN7HteWpa2Q3GVlgfr4qMd8ICZfRcobEDsOZuuaIXLQTbzoz0NXQdLFRr3C8EgfABfVrFyIDHPDAQjCNUoOpCfsW4IeHc5e/JCt+DjNkCtXpBg7gNqSSS7cx1zU0QA8P4tLiHbZwAbenrlU= Received: by 10.65.112.18 with SMTP id p18mr1791481qbm; Tue, 04 Oct 2005 09:30:55 -0700 (PDT) Received: by 10.65.158.12 with HTTP; Tue, 4 Oct 2005 09:30:55 -0700 (PDT) Message-ID: Date: Tue, 4 Oct 2005 12:30:55 -0400 From: solarsail Reply-To: solarsail To: pgsql-sql@postgresql.org Subject: using pg_tables and tablename in queries MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_15228_4497567.1128443455315" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.1 required=5 tests=[AWL=-0.052, HTML_10_20=0.295, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024, RCVD_IN_BL_SPAMCOP_NET=1.832] X-Spam-Level: ** X-Archive-Number: 200510/5 X-Sequence-Number: 22858 ------=_Part_15228_4497567.1128443455315 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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. ) ------=_Part_15228_4497567.1128443455315 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 ta= blename 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. )

------=_Part_15228_4497567.1128443455315--