YAIQ (yet another inane question) - Mailing list pgsql-general

    Actually, this isn't so inane... Let's say I have table A keeping track
of other tables, and those tables detail bandwidth usage.  I want to do
a subselect like the flowing, but I'm not sure how.  Take a look.  Any
ideas?  Thanks.

-g2


SELECT *,
SUM(octets)
FROM (
        SELECT table_name
        FROM flow_tables
        WHERE start_time >= '2000-08-10 00:00'  -- note the time format
is important
        AND end_time <= '2000-08-10 00:00'      -- same note as above
        )
WHERE src_addr <<= '10.1.1.0/24'::inet         -- CIDR network
designation
OR    dst_addr <<= '10.1.1.0/24'::inet         -- no / means /32
ORDER BY start_time
;

Here is what the tables involved look like...

CREATE TABLE flow_tables (
        key                     SERIAL          PRIMARY KEY,
        table_name              VARCHAR(25)     NOT NULL,
        period                  INTERVAL        NOT NULL,
        start_time              TIMESTAMP       NOT NULL,
        end_time                TIMESTAMP       NOT NULL,
        flows                   INT4            NOT NULL,
        missed                  INT4            NOT NULL,
        records                 INT4            NOT NULL,
        date_entered            TIMESTAMP       DEFAULT
CURRENT_TIMESTAMP NOT NULL
);

...and every hour, a table is created and populated that looks like
this...

CREATE TABLE flows_<unique_number> (
        src_addr                INET            NOT NULL,
        dst_addr                INET            NOT NULL,
        src_port                VARCHAR(12)     NOT NULL,
        dst_port                VARCHAR(12)     NOT NULL,
        protocol                VARCHAR(12)     NOT NULL,
        pkts                    INT4            NOT NULL,
        octets                  INT4            NOT NULL,
        flows_at_start_time     INT4            NOT NULL,
        start_time              TIMESTAMP       NOT NULL,
        end_time                TIMESTAMP       NOT NULL
);