User entry of parameters in queries/views.

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/
___________________________________________