Update row and variable in PostgreSQL function

Question

I have the following table structure:

CREATE TABLE perf
(
startDate DATE,
performance DOUBLE PRECISION,
indexLevel  DOUBLE PRECISION
PRIMARY KEY (startDate)
);

The columns startDate and performance are filled in a previous step. Now I want to set the indexLevel in a time-efficient way. In MSSQL this was done like:

SET @Index = 100;
UPDATE perf SET @Index = @Index * (1+performance), indexLevel = @Index;

How could this be implemented in PostgreSQL?

Is there a better solution than iterating through the table like here: Iterate through table, perform calculation on each row

Please provide sample data and desired rsults.

Gordon Linoff
– Gordon Linoff

2021-05-11 11:11:20 +00:00
Commented May 11, 2021 at 11:11 — Gordon Linoff
– Gordon Linoff, Commented May 11, 2021 at 11:11

Gordon Linoff · Accepted Answer · 2021-05-11 11:20:57Z

1

If I understand correctly, you want a cumulative product. One method uses arithmetic for the calculation:

select p.*,
       100 * exp(sum(1 + ln(performance)) over (order by startdate))
from perf p;

You can incorporate this into an update:

update perf p
    set indexLevel as p2.calculated_indexLevel
    from (select p.*,
                 100 * exp(sum(1 + ln(performance)) over (order by startdate)) as calculated_indexLevel
          from perf p
         ) p2
    where p2.startdate = p.startdate

answered May 11, 2021 at 11:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update row and variable in PostgreSQL function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related