Skip site navigation (1) Skip section navigation (2)

Re: Oracle Analytical Functions

From: Willem Buitendyk <willem(at)pcfish(dot)ca>
To: Adam Rich <adam(dot)r(at)sbcglobal(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Oracle Analytical Functions
Date: 2008-01-30 23:38:48
Message-ID: 47A10A88.4080602@pcfish.ca (view raw or flat)
Thread:
Lists: pgsql-general
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
>
>   


In response to

Responses

pgsql-general by date

Next:From: Oleg BartunovDate: 2008-01-30 23:40:55
Subject: Re: PostgreSQL Certification
Previous:From: Jeremy HarrisDate: 2008-01-30 23:30:00
Subject: Re: enabling autovacuum

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group