Remplacement des valeurs d'espace réservé par les données d'une autre table

Si vous souhaitez utiliser des espaces réservés nommés de type Python, vous avez besoin de la fonction d'assistance écrite sur plpythonu :

select t1.id, formatpystring(t1.value, json_object_agg(t2.placeholder, t2.value)) as value
from table1 as t1, table2 as t2
group by t1.id, t1.value;

Puis test simple:

select formatpystring('{foo}.{bar}', '{"foo": "win", "bar": "amp"}');
 formatpystring 
----------------
 win.amp

Enfin, vous devez composer ces arguments à partir de vos tables. C'est simple:

create extension plpythonu;

create or replace function formatpystring( str text, a json ) returns text immutable language plpythonu as $$
import json
d = json.loads(a)
return str.format(**d)
$$;

(La requête n'a pas été testée mais vous avez le sens)

Implémentation SQL dynamique (maladroite), avec une jointure externe, mais générant un appel de fonction récursif:

Cette fonction ne sera pas très efficace, mais la table de traduction est probablement relativement petite.

XXX

Résultats:

CREATE FUNCTION
NOTICE:  script=CREATE FUNCTION my_function (_arg1 text) RETURNS text AS
    $omg$
    BEGIN
    RETURN replace(replace(replace(_arg1, '{BBB}', '/1.2.3.4/'), '{ccc}', 'OMG'), '{ddd}', '/4.3.2.1/');
    END;
    $omg$ LANGUAGE plpgsql;
 create_replacement_function 
-----------------------------

(1 row)

         my_function         
-----------------------------
 aaa/1.2.3.4/ccc/4.3.2.1/eee
(1 row)

      my_function       
------------------------
 {AAA}bbb{CCC}DDD}{EEE}
(1 row)

La méthode ci-dessus a un comportement quadratique (par rapport au nombre d'entrées xlat) ; ce qui est horrible

Mais, nous pourrions créer dynamiquement une fonction (une fois) et l'appeler plusieurs fois (générateur d'un pauvre)

En sélectionnant uniquement les entrées pertinentes de la table xlat , il faudrait probablement ajouter. p>

Et, vous devez bien sûr recréer la fonction à chaque fois que la table xlat est modifiée.

CREATE FUNCTION create_replacement_function(_name text) RETURNS void
AS
$func$
DECLARE
        argname text;
        res text;
        script text;
        braced text;
        found record; -- (aa text, bb text, xx text);
BEGIN

script := '';
argname := '_arg1';
res :=format('%I', argname);

for found IN SELECT xy.aa,xy.bb
        FROM xlat_table xy
        LOOP
        -- RAISE NOTICE 'aa=%', found.aa;
        -- RAISE NOTICE 'bb=%', found.bb;
        -- RAISE NOTICE 'Res=%', res;
        braced := '{'|| found.aa || '}';
        script := format ('replace(%s, %L, %L)'
                        ,res,braced,found.bb);
                res := format('%s', script);
        END LOOP;

script :=FORMAT('CREATE FUNCTION %I (_arg1 text) RETURNS text AS
        $omg$
        BEGIN
        RETURN %s;
        END;
        $omg$ LANGUAGE plpgsql;', _name, script);

RAISE NOTICE 'script=%', script;

EXECUTE script ;

return ;
END;

$func$
LANGUAGE plpgsql;

SELECT create_replacement_function( 'my_function');

SELECT my_function('aaa{BBB}ccc{ddd}eee' );
SELECT my_function( '{AAA}bbb{CCC}DDD}{EEE}' );

Et le résultat:

CREATE TABLE
INSERT 0 3
CREATE FUNCTION
     dothe_replacements      
-----------------------------
 aaa/1.2.3.4/ccc/4.3.2.1/eee
(1 row)

    dothe_replacements    
--------------------------
 '{AAA}bbb{CCC}DDD}{EEE}'
(1 row)

Ce qui suit propose une solution plpgsql dans un avec une seule fonction. Vous remarquerez que j'ai «renommé» la colonne de valeur. C'est une mauvaise pratique d'utiliser des mots rserved / key comme noms d'objets. Soq est également le schéma que j'utilise pour tout le code SO.
Le processus prend d'abord les valeurs de détenteur de table2 et génère un ensemble de paires clé-valeur (dans ce cas, hstore, mais jsonb fonctionnerait également). Il construit ensuite un tableau à partir de la colonne de valeur (nom de ma colonne: chaîne_val) contenant le nom de l'espace réservé à partir de la valeur. Enfin, il itère ce tableau en remplaçant le nom du détenteur réel par la valeur des valeurs-clés en utilisant la valeur du tableau comme clé de recherche.
La performance ne serait pas excellente avec un volume plus important de l'une ou l'autre table. Si vous devez traiter un volume important à la fois en une seule ligne, la table temporaire peut donner de meilleures performances.

select id, soq.replace_holders(val_string) result_value from soq.table1;

- Pilote de test

create or replace function soq.replace_holders( place_holder_line_in text)
 returns text
language plpgsql
as $$
declare 
    l_holder_values hstore;
    l_holder_line   text; 
    l_holder_array  text[];
    l_indx          integer;

begin
    -- transform cloumns to key-value pairs of holder-value
    select string_agg(place,',')::hstore
      into l_holder_values
      from (
             select concat( '"',place_holder,'"=>"',place_value,'"')  place 
               from soq.table2 
           ) p; 
   -- raise notice 'holder_array_in==%',l_holder_values;

    -- extract the text line and build array of place_holder names
    select phv, string_to_array (string_agg(v,','),',')  
      into l_holder_line,l_holder_array
      from (
            select replace(replace(place_holder_line_in,'{',''),'}','') phv
                 , replace(replace(replace(regexp_matches(place_holder_line_in,'({[^}]+})','g')::text ,'{',''),'}',''),'"','') v 
           ) s 
       group by phv;

    -- raise notice 'Array==%',l_holder_array::text;
    -- replace each key from text line with the corresponding value
    for l_indx in 1 .. array_length(l_holder_array,1)
    loop
        l_holder_line = replace(l_holder_line,l_holder_array[l_indx],l_holder_values -> l_holder_array[l_indx]);
    end loop;

    -- done
    return l_holder_line;       
end;
$$;

J'ai créé une requête simple pour cette solution et elle fonctionne comme requis.

id                                      value
CDA4C3D4-72B5-4422-8071-A29D32BD14E0    https://10.90.30.40/svc/mytest-service/
608CB424-90BF-4B08-8CF8-241C7635434F    jdbc:postgresql://1.2.3.4:5432/mytest

Le résultat est

WITH RECURSIVE cte(id, value, level) AS (
        SELECT id,value, 0 as level
        FROM Table1 
      UNION 
        SELECT ts.id,replace(ts.value,'{'||tp.placeholder||'}',tp.value) as value, level+1
    FROM cte ts, Table2 tp WHERE ts.value LIKE CONCAT('%',tp.placeholder, '%')
)
SELECT id, value FROM cte c
 where  level =
(
  select Max(level)
  from cte c2  where c.id=c2.id
)