lundi 20 avril 2015

How to replace all subsets of characters based on values of other tables in pl/pgsql?

I've been doing some research on how to replace a subset of string of characters of a single row base on the values of the columns of other rows, but was not able to do so since the update are only for the first row values of the other table. So I'm planning to insert this in a loop in a plpsql function.

Here are the snippet of my tables. Main table:

 Table "public.tbl_main"
        Column         |  Type  | Modifiers 
 maptarget             | text   | 
 expression            | text   | 

 maptarget |                 expression
 43194-0   | 363787002:70434600=(386053000:704347000=(414237002:704320005=259470008,704318007=118539007,704319004=50863008),704327008=122592007,246501002=703690001,370132008=30766002)

Look-up table:

Table "public.tbl_values"
        Column         |  Type  | Modifiers 
 conceptid             | bigint | 
 term                  | text   |

 conceptid |                   term                   
 386053000 | Patient evaluation procedure (procedure)
 363787002 | Observable entity (observable entity)
 704347000 | Observes (attribute)
 704320005 | Towards (attribute)
 704318007 | Property type (attribute)

I want to create a function that will replace all numeric values in the tbl_main.expression columns with their corresponding tbl_values.term using the tbl_values.conceptid as the link to each numeric values in the expression string.

I'm stuck currently in the looping part since I'm a newbie in LOOP of plpgsql. Here is the rough draft of my function.

--create first a test table
drop table if exists tbl_test;
create table tbl_test as select * from tbl_main limit 1;

create or replace function test () 
 LANGUAGE plpgsql
AS $function$
 resultItem tbl_main;
 v_mapTarget text;
 v_expression text;
 ctr int;

  for resultItem in (select * from tbl_test) loop
     select into v_expression expression from ee;
     raise notice 'parameter used: %',v_mapTarget;
     raise notice 'current expression: %',v_expression;

     update ee set expression=replace(v_expression, new_exp::text, term) from (select new_exp::text, term from tbl_values offset ctr limit 1) b ; 
     raise notice 'counter: %', ctr;
     v_expression:= (select expression from ee);
     resultItem.expression:= v_expression;
     raise notice 'current expression: %',v_expression;
return next resultItem;
 end loop;

Any further information will be much appreciated.
My Postgres version:

PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

Aucun commentaire:

Enregistrer un commentaire