X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 8D8C930E764 for ; Thu, 19 Jun 2003 15:37:24 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 74711-04 for ; Thu, 19 Jun 2003 12:37:14 -0300 (ADT) Received: from bach.ccinet.ab.ca (bach.ccinet.ab.ca [198.161.96.1]) by svr1.postgresql.org (Postfix) with ESMTP id 48FF330E6D9 for ; Thu, 19 Jun 2003 12:37:13 -0300 (ADT) Received: from incentre.net (squid.incentre.net [206.75.213.195]) by bach.ccinet.ab.ca (8.12.6/8.12.6) with ESMTP id h5JFeZ7j012949; Thu, 19 Jun 2003 09:40:36 -0600 (MDT) (envelope-from guy@incentre.net) Message-ID: <3EF1D8A3.9040209@incentre.net> Date: Thu, 19 Jun 2003 09:37:07 -0600 From: Guy Fraser User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030609 X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Cc: gearond@cvc.net Subject: Re: [GENERAL] Request for advice: Table design References: <021301c334ab$633b79c0$09000a0a@kregert.se> <200306171906.10381.jasongodden@optushome.com.au> <3EEF6FC2.3020707@incentre.net> <3EEF7785.4090102@cvc.net> In-Reply-To: <3EEF7785.4090102@cvc.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200306/227 X-Sequence-Number: 13985 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 >