Version of Postgresql: 8.0.4
I have created a function:
-- Function: avg_max_speed_hr(timestamp, timestamp)
-- DROP FUNCTION avg_max_speed_hr("timestamp", "timestamp");
CREATE OR REPLACE FUNCTION avg_max_speed_hr("timestamp", "timestamp")
RETURNS SETOF t_avgmaxspeedhr AS
$BODY$
SELECT date_trunc('hour',ap.absolute_time), ap.agent_id, avg(ap.speed),
max(ap.speed)
FROM agent_position AS ap
WHERE ap.absolute_time BETWEEN $1 AND $2
GROUP BY date_trunc('hour',ap.absolute_time),ap.agent_id
HAVING avg(ap.speed)>0
ORDER BY date_trunc('hour',ap.absolute_time),ap.agent_id
$BODY$
LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION avg_max_speed_hr("timestamp", "timestamp") OWNER TO fleetmgt;
COMMENT ON FUNCTION avg_max_speed_hr("timestamp", "timestamp") IS 'This
function returns a table of agents, their average and maximum speeds for
hourly intervals over the time interval specified.';
Which I can access in a query like this:
SELECT * FROM avg_max_speed_hr('2005-06-16 00:00:00','2005-06-17 00:00:00 ');
This works OK.
My problem is that users are using MS Access via the ODBC link to access the
database and Postgresql functions (like avg_max_speed_hr() ) are not being
recognised.
I had assumed that I could wirte in Access something like this:
SELECT * FROM avg_max_speed_hr(StartTIme,EndTime); -- *
Where prompts for the user defined variables would appear when the query is
run.
I am only new to Postgresql so I am asking if it is possible to create a view
similar to the above query(*) that can be accessed through Access.
I can rewrite it as a query in Access e.g.
SELECT Format(ap.absolute_time,"yyyy mm dd") AS [Day],
Format(ap.absolute_time,"hh") AS [Hour], Format(ap.absolute_time,"yyyy mm dd
hh") AS TimePeriod, ap.agent_id, Avg(ap.speed) AS AvgOfspeed, Max(ap.speed)
AS MaxOfspeed
FROM public_agent_position AS ap
WHERE (((ap.absolute_time) Between [StartTime] And [EndTime]))
GROUP BY Format(ap.absolute_time,"yyyy mm dd"), Format(ap.absolute_time,"hh"),
Format(ap.absolute_time,"yyyy mm dd hh"), ap.agent_id
HAVING (((Avg(ap.speed))>0))
ORDER BY Format(ap.absolute_time,"yyyy mm dd hh"), ap.agent_id;
However this runs quite slowly and I would rather defer the processing to the
server. It also poses problems in graphing because of the group statements.
Thanks for any help.
Andrew
--
___________________________________________
Andrew J. P. Maclean
Postal:
Australian Centre for Field Robotics
The Rose Street Building J04
The University of Sydney 2006 NSW
AUSTRALIA
Room:106
Phone:+61 2 9351 3283
Fax:+61 2 9351 7474
http://www.acfr.usyd.edu.au/
___________________________________________
