From: Guy Fraser <guy@incentre.net>
To: pgsql-sql@postgresql.org
Cc: gearond@cvc.net
Subject: Re: [GENERAL] Request for advice: Table design
Date: Thu, 19 Jun 2003 09:37:07 -0600
Message-ID: <3EF1D8A3.9040209@incentre.net> (raw)
In-Reply-To: <3EEF7785.4090102@cvc.net>
References: <021301c334ab$633b79c0$09000a0a@kregert.se>
<200306171906.10381.jasongodden@optushome.com.au>
<Pine.LNX.4.53.0306170936050.8304@perrin.socsci.unc.edu>
<3EEF6FC2.3020707@incentre.net>
<3EEF7785.4090102@cvc.net>
Hi
I realized I made a mistake. :(
The example below should have :
create view all_data as
select ...
The "as" statement was missing.
Here is a real sample of a function allowing a similar effect :
----------- sql script ---------------
--
-- delete old function [must be done before type is dropped]
--
DROP FUNCTION acct_info( TEXT , INTEGER );
--
-- delete old type
--
DROP TYPE acct_info_record;
--
-- return type for acct_info records
--
CREATE TYPE acct_info_record AS (
"Time-Stamp" ABSTIME,
"Acct-Status-Type" TEXT,
"User-Name" TEXT,
"Realm" TEXT,
"Acct-Session-Time" INTEGER,
"Acct-Input-Octets" INTEGER,
"Acct-Output-Octets" INTEGER,
"Called-Station-Id" TEXT,
"Calling-Station-Id" TEXT,
"Acct-Terminate-Cause" TEXT,
"Framed-IP-Address" INET,
"Service-Type" TEXT,
"Framed-Protocol" TEXT,
"Client-IP-Address" INET,
"NAS-IP-Address" INET,
"NAS-Port-Type" TEXT,
"NAS-Port-Id" INTEGER,
"Timestamp" INTEGER,
"Acct-Session-Id" TEXT,
"Acct-Link-Count" SMALLINT,
"Acct-Multi-Session-Id" TEXT,
"Acct-Delay-Time" INTEGER
);
--
-- function to select start and stop records as one data set by "mon"
and year.
--
CREATE FUNCTION acct_info( TEXT , INTEGER ) RETURNS SETOF
acct_info_record AS '
DECLARE
p_mon ALIAS FOR $1;
p_year ALIAS FOR $2;
v_exec TEXT;
rec RECORD;
BEGIN
v_exec := ''SELECT
"Time-Stamp",
"Acct-Status-Type",
"User-Name",
"Realm",
"Acct-Session-Time",
"Acct-Input-Octets",
"Acct-Output-Octets",
"Called-Station-Id",
"Calling-Station-Id",
"Acct-Terminate-Cause",
"Framed-IP-Address",
"Service-Type",
"Framed-Protocol",
"Client-IP-Address",
"NAS-IP-Address",
"NAS-Port-Type",
"NAS-Port-Id",
"Timestamp",
"Acct-Session-Id",
"Acct-Link-Count",
"Acct-Multi-Session-Id",
"Acct-Delay-Time"
FROM acct_start_'' || p_year || p_mon ||
'' UNION SELECT
"Time-Stamp",
"Acct-Status-Type",
"User-Name",
"Realm",
"Acct-Session-Time",
"Acct-Input-Octets",
"Acct-Output-Octets",
"Called-Station-Id",
"Calling-Station-Id",
"Acct-Terminate-Cause",
"Framed-IP-Address",
"Service-Type",
"Framed-Protocol",
"Client-IP-Address",
"NAS-IP-Address",
"NAS-Port-Type",
"NAS-Port-Id",
"Timestamp",
"Acct-Session-Id",
"Acct-Link-Count",
"Acct-Multi-Session-Id",
"Acct-Delay-Time"
FROM acct_stop_'' || p_year || p_mon ;
FOR rec IN EXECUTE v_exec
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';
--
-- check to make sure it works
--
SELECT * FROM acct_info('jun','2003') LIMIT 10;
--
------- end of sql script ----------
That may not be of as much help for that project, but it was somthing I
realized I could use in one of my applications.
Guy
Dennis Gearon wrote:
> wow! Thanks for that info. I'm definitely filing this for use in a
> future,near term project.
>
> Guy Fraser wrote:
>
>> Hi
>>
>> As an additional note;
>>
>> Older data is moved into a seperate table to reduce the number of
>> records that require regular vacuuming. Since the tables would
>> contain similar data it is simple to use union selections in a view
>> with an additional column to indicate which table the data comes
>> from. Using a view that combines the data from the two tables using a
>> union, the data will appear to be comming from a single table. This
>> method make archival access transparent.
>>
>> I have a realtime data collection system that I built. The data is
>> put into tables on a yearly and monthly basis on the fly and new
>> tables are created as needed. I use a union to join tables to access
>> the data over several months. I just thought of a new idea, I am
>> going to write a function to join the tables required over a timespan
>> - but that's another story.
>>
>> Two tables are easy to join with a union :
>> {if the column types are exactly matched}
>>
>> create view all_data
>> select *,'current_data'::text as data_table from current_data ...
>> union
>> select *,'archive_data'::text from archive_data ...
>> ;
>>
>> The last column will indicate the data's origin.
>>
>> Now to see all the data :
>>
>> select * from all_data ;
>>
>> Thats about it, using this method allows the "dynamic" table to small
>> for quick maintenace and operation, while the "static" table needs
>> less maintenace so it can be large with out the penalties incurred by
>> frequent maintenace.
>>
>> Guy
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: guy@incentre.net, gearond@cvc.net
Subject: Re: [GENERAL] Request for advice: Table design
In-Reply-To: <3EF1D8A3.9040209@incentre.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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