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 () 
 RETURNS SETOF tbl_main
 LANGUAGE plpgsql
AS $function$
declare
 resultItem tbl_main;
 v_mapTarget text;
 v_expression text;
 ctr int;
begin
  v_mapTarget:='';
  v_expression:='';
  ctr:=1;

  for resultItem in (select * from tbl_test) loop
     v_mapTarget:=resultItem.mapTarget;
     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 ; 
     ctr:=ctr+1;
     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;
 return;
 end;
$function$;

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