Postgresql stored procedure with if then condition on updating table

Question

I am new to postgresql and am trying to update a table based on conditions using PostgreSQL stored procedure. The table 'pref2' looks like this:

geneid              pred_perf_r2        pred_perf_pval      vers
ENSG00000107959     0.03                 0.02                1.0
ENSG00000106321     0.05                 0.01                1.0
ENSG00000102222     0.22                 0.05                1.0
ENSG00000101111     0.11                 0.03                1.0
ENSG00000102355     0.33                 0.01                1.0

I want to create a stored procedure for updating this table for pred_perf_r2 and pred_perf_pval if the new scores are better (bigger R2 and smaller pval). My attempt:

create or replace procedure new_version(
gene varchar(50),
new_r2 numeric,
new_p numeric,
new_v numeric   
)
language plpgsql
as $$
begin
if (new_r2 > perf2.pred_perf_r2)
and (new_p < perf2.pred_perf_pval) then
    update perf2 set
    perf2.pred_perf_r2 = new_r2,

    perf2.pred_perf_pval = new_p ,
    
    perf2.vers = new_v

where perf2.geneid = gene;
end if;
commit;
END;$$

call new_version('ENSG00000107959',0.55,0.01,2.0);
select * from perf2;

It gives me this error:

ERROR:  missing FROM-clause entry for table "perf2"
LINE 1: (new_r2 > perf2.pred_perf_r2)
                  ^
QUERY:  (new_r2 > perf2.pred_perf_r2)
and (new_p < perf2.pred_perf_pval)
CONTEXT:  PL/pgSQL function new_version(character varying,numeric,numeric,numeric) line 3 at IF
SQL state: 42P01

My desired result will look like this when calling the stored procedure:

geneid              pred_perf_r2        pred_perf_pval      vers
ENSG00000107959     0.55                 0.01                2.0
ENSG00000106321     0.05                 0.01                1.0
ENSG00000102222     0.22                 0.05                1.0
ENSG00000101111     0.11                 0.03                1.0
ENSG00000102355     0.33                 0.01                1.0

if

call new_version('ENSG00000107959',0.02,0.05,2.0);

The original table should not change since R square is worse (0.02 < 0.03) and pval is larger (0.05>0.02) It keeps giving me errors. Any ideas on how I can fix this?

I think the problem is taking columns as parameters in the if statement; I cant find a way to do it. Any help will be appreciated! — Iwishworldpeace
– Iwishworldpeace, Commented Nov 5, 2022 at 6:12

Atmo · Accepted Answer · 2022-11-05 07:07:08Z

1

The problem with your procedure is that you cannot guarantee nobody will bypass it by writing a regular UPDATE statement.
You can solve this with a trigger or a rule.

Solution 1: Trigger

Solution 1 will raise an exception, which:

client applications can catch and eventually react to (useful to remember if you perform the UPDATE in the middle of a transaction).
means, when updating several records, 1 error will block the whole statement.

Code:

CREATE OR REPLACE FUNCTION RaiseError() RETURNS TRIGGER
AS $$
BEGIN
RAISE EXCEPTION USING MESSAGE = 'Invalid r-square or pval', ERRCODE = '23514' /*check_violation*/;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER CheckScoreUpdate BEFORE UPDATE OF pred_perf_r2, pred_perf_pval ON perf2
FOR EACH ROW
WHEN (NEW.pred_perf_r2 < OLD.pred_perf_r2 OR NEW.pred_perf_pval > OLD.pred_perf_pval)
EXECUTE FUNCTION RaiseError()

Solution 2: Rule

Solution 2 skips records breaking your rule and will not raise any error, which:

means records get updated when valid, even if another record being updated in the same statement is not.
prevents client applications from knowing if all the records they intended to update were effectively updated (unless you know in advance how many records you were attempting to update).

Code:

CREATE OR REPLACE RULE CheckScoreUpdate AS ON UPDATE TO perf2
WHERE NEW.pred_perf_r2 < OLD.pred_perf_r2 OR NEW.pred_perf_pval > OLD.pred_perf_pval
DO INSTEAD NOTHING

edited Nov 5, 2022 at 7:07

answered Nov 5, 2022 at 6:55

Atmo

4,2621 gold badge7 silver badges32 bronze badges

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

1 Comment

Iwishworldpeace Over a year ago

OMG, thank you! The setting of the predetermined rule is a lifesaver.

Adrian Klaver · Accepted Answer · 2022-11-05 14:44:39Z

1

As the error states this, perf2.pred_perf_r2, is table reference for a table that does not exist in the function context. Same will happen with perf2.pred_perf_pval. The other issue is that neither value is filtered to a specific gene. Also you can't table specify the updated columns so this perf2.pred_perf_r2 = new_r2 needs to be pred_perf_r2 = new_r2, same for the other columns. You would need to do something like:

create or replace procedure new_version(
gene varchar(50),
new_r2 numeric,
new_p numeric,
new_v numeric   
)
language plpgsql
as $$
DECLARE
    old_pred_perf_r2 numeric;
    old_pred_perf_pval numeric;
begin
   SELECT pred_perf_r2, pred_perf_pval INTO 
      old_pred_perf_r2, old_pred_perf_pval
   FROM 
      perf2
   WHERE
     geneid = gene;
   if (new_r2 > old_pred_perf_r2)
   and (new_p < old_pred_perf_pval) then
       update perf2 set
        pred_perf_r2 = new_r2,
        pred_perf_pval = new_p ,
        vers = new_v
        where perf2.geneid = gene;
   end if;
commit;
END;$$

Option B. Put the if logic in the UPDATE statement.

create or replace procedure new_version(
gene varchar(50),
new_r2 numeric,
new_p numeric,
new_v numeric   
)
language plpgsql
as $$

begin
   update perf2 set
        pred_perf_r2 = new_r2,
        pred_perf_pval = new_p,
        vers = new_v
   where 
     perf2.geneid = gene
   and 
      new_r2 > pred_perf_r2
   and
      new_p < pred_perf_pval      
;
   end if;
commit;
END;$$

edited Nov 5, 2022 at 14:44

answered Nov 5, 2022 at 14:32

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

2 Comments

Iwishworldpeace Over a year ago

Thanks! I was looking for this originally about declaring variables. This solves the no reference issue, which is the source of my error.

Adrian Klaver Over a year ago

See my update with an option that eliminates the variables.

Collectives™ on Stack Overflow

Postgresql stored procedure with if then condition on updating table

2 Answers 2

Solution 1: Trigger

Solution 2: Rule

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Solution 1: Trigger

Solution 2: Rule

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related