sub3 napisal 14.11.2008 20:12:
Hi,
I was hoping someone could help me build a better query. I have a table of
time/locations. Occasionally, we have multiple timestamps for the same
location. I would like to remove those extra timestamps and only show the
transition from one location to another. So...
create table time_locations (
id integer,
timestamp double precision,
location integer
)
Try to not use data type name for column name.
Data:
1,1197605841,1
2,1197608001,2
3,1197609802,2
4,1197611951,2
5,1199145360,2
6,1199145480,3
7,1199147280,3
8,1199149140,3
9,1199151300,1
10,1199152000,3
I would like to return a table like:
1197605841,1,1197608001,2
1199145360,2,1199145480,3
1199149140,3,1199151300,1
1199151300,1,1199152000,3
The only way I can think of to do this would be a procedure which would do a
large loop over the
table (sorted by time) returning a row when last.location <> this.location.
However, when I try this on a
large table, it seems like the 'select into' doesn't order & ruins the whole
solution.
Select into is used for fetching single row result. You need rather
for..in loop
Is there a query approach?
Example below gives the same result as described:
CREATE OR REPLACE FUNCTION location_changes(
last_time OUT double precision, last_location OUT integer,
new_time OUT double precision, new_location OUT integer
) RETURNS setof RECORD AS $$
BEGIN
for new_location, new_time in select location,timestamp
from time_locations order by timestamp
loop
if last_location<>new_location then
return next;
end if;
last_location=new_location;
last_time=new_time;
end loop;
END;
$$ language 'plpgsql';
select * from location_changes();
--
Regards,
Tomasz Myrta
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
