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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
