How to create function and call it in PostgreSQL for removing duplicate code-CodePudding

My need is to remove the duplicate script code. As we can see in the mentioned code block, I Need to run the update command on the same table in the same manner for multiple arrays. Just that the data passed in the where clause is different. Currently, the code is duplicated as I have written multiple loops. Can someone help me reduce the code line by giving a different approach like how we can use the function or something else?

DO
$do$
DECLARE
 m   text[];
 acc  text[] := '{{acc_PROF,1},{acc_PER,2},{cad_PROF,3}}';
 lev  text[] := '{{lev_ADMIN,1},{lev_PROF,2}}';
 att  text[] := '{{att_ADMIN,1},{att_PROF,2}}';
BEGIN
 --For acc profile template
 FOREACH m SLICE 1 IN ARRAY acc
 LOOP
  update profile set order_num =CAST (m[2] as bigint) where persistent_id =m[1]; 
 END LOOP;

  --For lev profile template
    FOREACH m SLICE 1 IN ARRAY lev
 LOOP
  update profile set order_num =CAST (m[2] as bigint) where persistent_id =m[1]; 
 END LOOP;

  --For att profile template
 FOREACH m SLICE 1 IN ARRAY att
     LOOP
      update profile set order_num =CAST (m[2] as bigint) where persistent_id =m[1]; 
   END LOOP;

END
$do$
LANGUAGE plpgsql
;

CodePudding user response：

You can use simple sql command.

CREATE temp TABLE profile (
    order_num bigint,
    persistent_id text
);

INSERT INTO profile (persistent_id)
    VALUES ('acc_PROF'),
    ('acc_PER'),
    ('cad_PROF'),
    ('lev_ADMIN'),
    ('lev_PROF'),
    ('att_ADMIN'),
    ('att_PROF');

Then

WITH cte (
    persistent_id,
    order_sum
) AS (
    VALUES ('acc_PROF', 1),
        ('acc_PER', 2),
        ('cad_PROF', 3),
        ('lev_ADMIN', 1),
        ('lev_PROF', 2),
        ('att_ADMIN', 1),
        ('att_PROF', 2))
UPDATE
    profile
SET
    order_num = cte.order_sum
FROM
    cte
WHERE
    cte.persistent_id = profile.persistent_id
RETURNING
    *;