如何根据pl/pgsql中其他表的值替换所有字符子集? [英] How to replace all subsets of characters based on values of other tables in pl/pgsql?
问题描述
我一直在研究如何基于其他行的列值替换单个行的字符串的子集，但是由于更新仅用于第一个更新，因此无法这样做.另一个表的行值.因此，我打算将其插入到plpsql函数的循环中.
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)
查询表:
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)
我想创建一个函数，使用tbl_values.conceptid作为表达式字符串中每个数字值的链接，将tbl_main.expression列中的所有数字值替换为其相应的tbl_values.term.
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.
由于我是plpgsql的LOOP的新手，所以我目前停留在循环部分.这是我的功能的草稿.
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$;
任何进一步的信息将不胜感激.
我的Postgres版本:
Any further information will be much appreciated.
My Postgres version:
x86_64-unknown-linux-gnu上的PostgreSQL 9.3.6，由gcc编译(Ubuntu 4.8.2-19ubuntu1)4.8.2，64位
PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
推荐答案
具有动态SQL的PL/pgSQL函数
循环永远是最后的手段.即使在这种情况下，使用查询连接查询字符串并执行一次
CREATE OR REPLACE FUNCTION f_make_expression(_expr text, OUT result text) AS
$func$
BEGIN
EXECUTE (
SELECT 'SELECT ' || string_agg('replace(', '') || '$1,'
|| string_agg(format('%L,%L)', conceptid::text, v.term), ','
ORDER BY conceptid DESC)
FROM (
SELECT conceptid::bigint
FROM regexp_split_to_table($1, '\D+') conceptid
WHERE conceptid <> ''
) m
JOIN tbl_values v USING (conceptid)
)
USING _expr
INTO result;
END
$func$ LANGUAGE plpgsql;
致电:
SELECT *, f_make_expression(expression) FROM tbl_main;
但是，如果不是所有的conceptid都具有相同的数字位数，则该操作可能会模棱两可.首先用更多数字替换conceptid以避免-ORDER BY conceptid DESC这样做-并确保替换字符串不会引起歧义(下一步中可能要替换的数字).相关答案以及更多有关这些陷阱的信息:
However, if not all conceptid have the same number of digits, the operation could be ambiguous. Replace conceptid with more digits first to avoid that - ORDER BY conceptid DESC does that - and make sure that replacement strings do not introduce ambiguity (numbers that might be replaced in the the next step). Related answer with more on these pitfalls:
令牌 $1 在这里有两种不同的用法，请不要误导:
The token $1 is used two different ways here, don't be misled:
regexp_split_to_table($1, '\D+')这引用了第一个功能参数_expr.您也可以使用参数名称.
This one references the first function parameter _expr. You could as well use the parameter name.
|| '$1,'这将对通过USING子句传递给EXECUTE的第一个表达式的引用连接到SQL字符串中.外部函数的参数在EXECUTE内部不可见，您必须显式传递它们.
This concatenates into the SQL string a references to the first expression passed via USING clause to EXECUTE. Parameters of the outer function are not visible inside EXECUTE, you have to pass them explicitly.
纯粹的巧合是，外部函数的$1(_expr)作为$1传递给EXECUTE.最好在USING子句($3)中将$7作为第三个表达式移交...
It's pure coincidence that $1 (_expr) of the outer function is passed as $1 to EXECUTE. Might as well hand over $7 as third expression in the USING clause ($3) ...
我在小提琴中添加了调试功能.进行较小的修改后，您可以输出生成的SQL字符串以对其进行检查:
I added a debug function to the fiddle. With a minor modification you can output the generated SQL string to inspect it:
这里是纯SQL替代方法.可能还会更快:
Here is a pure SQL alternative. Probably also faster:
CREATE OR REPLACE FUNCTION f_make_expression_sql(_expr text)
RETURNS text AS
$func$
SELECT string_agg(CASE WHEN $1 ~ '^\d'
THEN txt || COALESCE(v.term, t.conceptid)
ELSE COALESCE(v.term, t.conceptid) || txt END
, '' ORDER BY rn) AS result
FROM (
SELECT *, row_number() OVER () AS rn
FROM (
SELECT regexp_split_to_table($1, '\D+') conceptid
, regexp_split_to_table($1, '\d+') txt
) sub
) t
LEFT JOIN tbl_values v ON v.conceptid = NULLIF(t.conceptid, '')::int
$func$ LANGUAGE sql STABLE;
在Postgres 9.4 中，它具有两个新功能会更加优雅:
In Postgres 9.4 this can be much more elegant with two new features:
-
ROWS FROM取代旧的(怪异的)技术来同步集返回功能 -
WITH ORDINALITY可靠地获取行号 :ROWS FROMto replacing the old (weird) technique to sync set-returning functionsWITH ORDINALITYto get row numbers on the fly reliably:- PostgreSQL unnest() with element number
CREATE OR REPLACE FUNCTION f_make_expression_sql(_expr text) RETURNS text AS $func$ SELECT string_agg(CASE WHEN $1 ~ '^\d' THEN txt || COALESCE(v.term, t.conceptid) ELSE COALESCE(v.term, t.conceptid) || txt END , '' ORDER BY rn) AS result FROM ROWS FROM ( regexp_split_to_table($1, '\D+') , regexp_split_to_table($1, '\d+') ) WITH ORDINALITY AS t(conceptid, txt, rn) LEFT JOIN tbl_values v ON v.conceptid = NULLIF(t.conceptid, '')::int $func$ LANGUAGE sql STABLE;SQL小提琴 演示了Postgres 9.3的全部内容.
SQL Fiddle demonstrating all for Postgres 9.3.
这篇关于如何根据pl/pgsql中其他表的值替换所有字符子集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
