Re: [GENERAL] Request for advice: Table design

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
>