CODEWITHSUNDEEP
postgresqlwindow-functions
dan
dan

Reputation: 1605

How can you do window functions in PostgreSQL 8.3

I have written a web app to be deployed to heroku. I wrote it using PostgreSQL 9.1.2. But it seems that the free shared db on heroku is only postgres 8.3. They are upgrading to 9. Something sometime soon, but until then, one of my SQL queries doesn't work. It is:

SELECT id, route_id, location_id, order_id, blogtext,
  lead(id) over (PARTITION BY route_id ORDER BY order_id ASC) AS next_id,
  lead(location_id) over (PARTITION BY route_id ORDER BY order_id ASC) AS next_location_id,
  lag(id) over (PARTITION BY route_id ORDER BY order_id ASC) as previous_id,
  lag(location_id) over (PARTITION BY route_id ORDER BY order_id ASC) AS previous_location_id,
  row_number() over (PARTITION BY route_id ORDER BY order_id ASC) AS indx

Is it possible to re-write this to work on PostgreSQL 8.3?

Upvotes: 0

Views: 2169

Answers (1)

vyegorov
vyegorov

Reputation: 22845

Please, note, that Window functions doesn't exists in PostgreSQL before 8.4.

EDIT2:

You made me really curios and I've played around with the queries.

Some things to note first:

  1. In order to achieve the desired result, you'll have to join table with itself many times. Given PostgreSQL 8.3 is not supporting CTE, I've created a view. Luckily in your case only one view is required, as you use the same OVER () conditions for all window functions;

  2. row_number should be used to operate on data subsets, so I've added this field to the view;

  3. To form PARTITION, you should use = operator on joined tables for all fields in the PARTITION clause;

  4. row_number is formed using subquery (no join) and counting all rows that match partition and have ORDER BY fields less or equal to the current one. Note, that if ORDER BY field is not unique, this approach will not work! If it is not the case, then create such field.

  5. To simulate ORDER BY clause on non row_number functions, you join on row_number fields, making it being +1, -1, min() or max() on the joined side, matching lead(), lag(), first_value() and last_value() functions respectively.

Please, consider point 4 above!

Your query can by changed the following way (compare output before using):

-- First, create a support view
CREATE VIEW table_v AS
SELECT id, route_id, location_id, order_id, blogtext,
       (SELECT count(*) FROM table
         WHERE route_id = t.route_id AND order_id <= t.order_id) AS row_number
  FROM table t;

-- Now, the query
SELECT t.id, t.route_id, t.location_id, t.order_id, t.blogtext,
       tlead.id AS next_id, tlead.location_id AS next_location_id,
       tlag.id AS previous_id, tlag.location_id AS previous_location_id,
       t.row_number AS indx
  FROM table_v t
  LEFT JOIN table_v tlead
    ON tlead.route_id = t.route_id AND tlead.row_number = t.row_number + 1
  LEFT JOIN table_v tlag
    ON tlag.route_id = t.route_id AND tlag.row_number = t.row_number - 1
  -- 
  -- proceed with the query here
  -- ...

I've used modified sample data from Window functions and more "local" aggregation and created a sandbox to show how it works on the SQL Fiddle.

Still, given all development was done on 9.1.2 I would think twice before going this way.

Upvotes: 2

Related Questions