I'm new to PostgresSQL and trying to do something a beyond my current skillset.

I'm using cursor to iterate through each row and set an incremental serial number if the Id is the same.

1. My query doesn't seem to fetch records, I'm testing by trying to set serialNumber to 1 and it's not working.
2. I'm not sure how to write increment by one if the Id is the same, otherwise reset serialNumber back to 1.

Please help!

CREATE OR REPLACE FUNCTION setSerial()
RETURNS int
LANGUAGE plpgsql
AS
$$
DECLARE 
    row_item record;
    
    cursorSub cursor for
        SELECT  "Id" AS uid, 
                "SerialNumber" AS SerialNumber
        FROM public."Drivers"
        GROUP BY "Id";

    BEGIN
        OPEN cursorSub;
        
            LOOP
                -- fetch the rows
                FETCH cursorSub INTO row_item;

                -- exit if no more rows
                EXIT WHEN NOT FOUND;

                -- build the output
                UPDATE public."Drivers"
                SET "SerialNumber" = 1 -- how to increment by one if Id is the name
                WHERE "id" = row_item.uid AND row_item.SerialNumber IS NULL;
            END LOOP;
        CLOSE cursorSub;
    END;
$$;

Here's a sample of the table:

CodePudding user response：

You can use a window function to generate the SerialNumber

select "Id", "ClientId", "SubmissionId", 
       row_number() over (partition by "ClientId" order by "Id") as "SerialNumber"
from "Drivers"

This can be used as the source for an UPDATE statement:

update "Drivers"
   set "SerialNumber" = t.sn
from (
  select "Id", 
         row_number() over (partition by "ClientId" order by "Id") as sn       
  from "Drivers"
  where "SerialNumber" is null
) t
where t."Id" = "Drivers"."Id"