I tried this function but it keeps returning an error such as:
ERROR: invalid input syntax for integer: "2007-05-05 00:34:08"
SQL state: 22P02
Context: PL/pgSQL function "lagfunc" line 10 at assignment
I checked and there are no datetime values in the client_id field
anywhere in my table 'all_client_times'
I have no idea what is going on here ...
Thanks for the code though - it has taught me a lot all ready; such as
using, OUT and SETOF Record
Willem
Adam Rich wrote:
and I would like to create a new view that takes the first table and
calculates the time difference in minutes between each row so that the
result is something like:
client_id,datetime, previousTime, difftime
122,2007-05-01 12:01:00, 2007-05-01 12:00:00, 1
455,2007-05-01 12:03:00, 2007-05-01 12:02:00, 1
455,2007-05-01 12:08:00, 2007-05-01 12:03:00, 5
299,2007-05-01 12:34:00, 2007-05-01 12:10:00, 24
Any idea how I could replicate this in SQL from PG. Would this be an
easy thing to do in Pl/pgSQL? If so could anyone give any directions
as to where to start?
You can create a set-returning function, that cursors over the table,
like this:
CREATE OR REPLACE FUNCTION lagfunc(
OUT client_id INT,
OUT datetime timestamp,
OUT previousTime timestamp,
OUT difftime interval)
RETURNS SETOF RECORD as $$
DECLARE
thisrow RECORD;
last_client_id INT;
last_datetime timestamp;
BEGIN
FOR thisrow IN SELECT * FROM all_client_times ORDER BY client_id,
datetime LOOP
IF thisrow.client_id = last_client_id THEN
client_id := thisrow.datetime;
datetime := thisrow.datetime;
previousTime := last_datetime;
difftime = datetime-previousTime;
RETURN NEXT;
END IF;
last_client_id := thisrow.client_id;
last_datetime := thisrow.datetime;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
select * from lagfunc() limit 10;
select * from lagfunc() where client_id = 455;
Here I used an interval, but you get the idea.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
