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?

CodePudding user response：

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

CodePudding user response：

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;$$