Postgresql – PL/pgsql: use cursor to update next record based on a condition from current record

plpgsqlpostgresql

I have this function in Postgresql 9.6 using pl/pgsql.
This function is to update a row from a table based on a condition on previous row.

The table has Item id(id), year of ownership (year) , quarter of ownership(quarter), own by ctc status(ownby_ctc), and
own by ftc status (ownby_ftc).

Each item id will be repeated one time each quarter every year from 2013 to 2016.
ownby_ctc will be marked 1 if item owned by CTC and it is either:
1- Happened before ownby_ftc or
2- Happened in the same quarter as ownby_ftc
and ownby_ctc never happened after ownby_ftc.

In case, ownby_ctc happened before ownby_ftc

I want to select the row where ownby_ctc = 1 and ownby_ftc = 0 and update the column ownby_ctc in next rows to be 1 until ownby_ftc is equal to 1.

Notice:
* the table is order by id, year, quarter.
*primary key is id_y_q.
Problem with my code is that the function is updating one row if the condition is true and this is because I am updating the actual table but not the cursor.

Please advice how to solve this problem.

CREATE OR REPLACE FUNCTION myfun()
   RETURNS VOID AS $$
DECLARE 

cur SCROLL CURSOR   
FOR select id_y_q, id, year, qrtr, ownby_ctc, ownby_ftc 
FROM mytbl ORDER BY id, year, qrtr;
r1 RECORD;
r2 RECORD;

BEGIN

OPEN cur ;

LOOP

    FETCH cur INTO r1;
    EXIT WHEN NOT FOUND;

    IF (r1.ownby_ctc = 1 and r1.ownby_ftc =0 )THEN

        MOVE FORWARD 0 FROM cur;
        FETCH cur INTO r2;
        EXIT WHEN NOT FOUND;

        IF (r1.id = r2.id) THEN
            IF ( r1.ownby_ctc = 0 AND r1.ownby_ftc =0  ) 
               OR (r1.ownby_ctc = 0 and r1.ownby_ftc =1  ) THEN 

                UPDATE mytbl
                SET r1.ownby_ctc = 0 = 1
                WHERE id_y_q = r2.id_y_q ;


                MOVE BACKWARD 1 FROM cur;

            END IF;
        END IF;

    END IF;

END LOOP;

CLOSE cur;

END; $$

LANGUAGE plpgsql;

Best Answer

What you want is something this this with lag() and lead()

UPDATE mytble
SET ownby_ctc = false
FROM (
    SELECT
      id_y_q,
      lead(ownby_ctc) OVER () AS lead_ownby_ctc,
      lead(ownby_ftc) OVER () AS lead_ownby_ctc,
    FROM mytbl
    ORDER BY id, year, qrtr;
) AS t2
WHERE t2.id_y_q = id_y_q
  AND t2.id = id
  AND ownby_ctc = 1
  AND ownby_ftc = 0

I'm not sure your copy works and you don't provide an environment but something like this is what you're after.

Related Solutions

Postgresql – Interactive INSERT / UPDATE function to implement UPSERT

If you want to catch NULL violations as well you have to cover the UPDATE as well, which makes the function a little more expensive.

SET search_path = netcen;

CREATE OR REPLACE FUNCTION fun_test(myobj test, OUT myoutput text)
  RETURNS text AS
$func$
BEGIN
   UPDATE test
   SET    tes = myobj.tes,
          testname = myobj.testname
   WHERE  testkey = myobj.testkey;

   IF FOUND THEN
      myoutput := 'UPDATE successfull. testkey = ' || myobj.testkey;
      RETURN;
   END IF;

   INSERT INTO test
   SELECT (myobj).*;
   myoutput := 'INSERT successfull. testkey = ' || myobj.testkey;

EXCEPTION
WHEN null_value_not_allowed THEN
   RAISE NOTICE 'null_value_not_allowed occurred.';
   myoutput := 'A field was null.';

WHEN not_null_violation THEN
   RAISE NOTICE 'not_null_violation occurred.';
   myoutput:= 'A field was null.';

WHEN unique_violation THEN
   RAISE NOTICE 'unique_violation occurred.';
   myoutput:= 'Duplicate value.';
END
$func$ LANGUAGE plpgsql;

Using an OUT parameter and made some simplifications and clarifications.

I would just let the regular EXCEPTION happen. People are not supposed to enter NULL values for NOT NULL columns. This way, the UPDATE can run outside the EXCEPTION block.

Postgresql – How to correctly return a query result only if it isn’t NULL

I don't think you need a cursor here at all. To shorten your code, you could just use a view. To improve performance, a materialized view should get you furthest. Postgres 9.3 has built-in features, but you can easily implemented it in older versions yourself.

Consider this simplified form:

CREATE FUNCTION store_distance(_lat    double precision
                              ,_long   double precision
                              ,_radius double precision
                              ,_tries  integer)
RETURNS TABLE(
   store_id   store.id%type
  ,store_name store.name%type
  ,distance   double precision) AS
$func$
DECLARE
   _ct  int   := 0;
   _pos point := point(_lat, _long);
BEGIN
   LOOP
      EXIT WHEN _ct >= _tries
           OR   EXISTS (
            SELECT 1 FROM store s
            WHERE  point(s.latitude, s.longitude) <@ circle(_pos, _radius));

      _radius := _radius * 2;
      _ct     := _ct + 1;
   END LOOP;

   RETURN QUERY
   SELECT s.id, s.name
         ,get_distance(_lat, _long, s.latitude, s.longitude)
   FROM   store s
   WHERE  point(s.latitude, s.longitude) <@ circle(_pos, _radius);
   ORDER  BY 3;
END
$func$ LANGUAGE plpgsql STRICT;

I made the function STRICT to disallow NULL input, which could result in an endless loop.

Note how I use circles with the "Contained" operator <@ instead of a boxes. One would assume calculations to be slightly more expensive than with boxes, but it hardly matters at all once you support your query with a GiST index like:

CREATE INDEX store_point_gist_idx ON store
USING gist (point(latitude, longitude));

You might consider to store lat / lon as point to begin with and replace the index on an expression with a simpler one on the column. Works either way, just make sure the query matches the index so it gets used. Big difference for big tables.

You may be interested in this closely related answer on SO I posted last year - with a lot more explanation and links.

Best Answer

Related Solutions

Postgresql – Interactive INSERT / UPDATE function to implement UPSERT

Postgresql – How to correctly return a query result only if it isn’t NULL

Related Question