在存储过程中的Postgres 8.4中转义LIKE模式或regexp字符串 [英] Escaping a LIKE pattern or regexp string in Postgres 8.4 inside a stored procedure
问题描述
我正在编写一个存储过程来查找主要项目的子项目并对其进行更新.情况就是这样
I am writing a stored procedure to find the sub-items of a main item and update it. The scenario is like this
Id Item Name Parent Id
1 Item A 14.1 NULL
2 Item B 14.1.1 1
3 Item C 14.1.2 1
4 Item B 14.1.3 1
5 Item A 14.1.1.1 2
我在SO 在PostgreSQL 8.4中的字符串匹配中发布了另一个问题得到一个项目的孩子.根据该答案,我必须转义项目代码,并在查询中使用它.但是我没有任何方法可以逃脱这一点. SP如下:
I have posted another question on SO String matching in PostgreSQL 8.4 to get the children of an item. According to that answer i have to escape the item code and have to use it in the query. But I haven't got any method to escape that. The SP is as below:
CREATE OR REPLACE FUNCTION updateitemparentnew(bigint, int) RETURNS text
LANGUAGE plpgsql STRICT
AS $$
DECLARE
itemdetail RECORD;
codeSearch text;
codeEscapedSearch text;
result text;
BEGIN
--Get th details of the current item
SELECT INTO itemdetail * FROM om_item WHERE item_id = $1;
codeSearch = itemdetail.item_code||'.';
codeEscapedSearch = codeSearch; --Need to be corrected. It should escape the item code
-- Event 1=> add 2 => edit 3 => delete
IF $2 = 1 THEN
--Find new children and update then
result = 'UPDATE om_item SET item_parentid = '||itemdetail.item_id
||' WHERE item_id IN (
SELECT item_id FROM om_item
WHERE item_code LIKE \''||codesearch||'%\'
AND item_code ~ \''||codeEscapedsearch||'[^.]+$\');';
END IF;
return result;
END;
$$;
在查询中应转义codeEscapedSearch
以处理代码本身中的.
和正则表达式中的.
.
In the query codeEscapedSearch
should be escaped to handle the .
in the code itself and the .
in the regex.
推荐答案
考虑这种直接方法:
CREATE OR REPLACE FUNCTION updateitemparentnew(_id bigint, _operation text)
RETURNS void LANGUAGE plpgsql STRICT AS
$func$
DECLARE
code_like text;
code_regex text;
BEGIN
SELECT INTO code_like, code_regex
p.item_code || '.%'
, '^' || replace(p.item_code, '.', '\.') || '\.[^.]+$'
FROM om_item p
WHERE p.item_id = _id;
CASE _operation -- ins / upd / del
WHEN 'upd' THEN -- Find new children and update then
UPDATE om_item c
SET item_parentid = _id
WHERE c.item_code LIKE code_like
AND c.item_code ~ code_regex;
-- WHEN 'ins' THEN ...
-- WHEN 'del' THEN ...
END CASE;
END
$func$;
这不返回查询字符串,而是直接执行UPDATE
.更短,更快.
This does not return a query string but executes the UPDATE
directly. Much shorter and faster.
还使用replace()
,顺便说一句.
彻底解决所有LIKE
和regexp模式的解决方案:
Thorough solution to escape all LIKE
and regexp patterns:
这篇关于在存储过程中的Postgres 8.4中转义LIKE模式或regexp字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!