Escaping a LIKE pattern or regexp string in Postgres 8.4 inside a stored procedure

Question

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

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;
$$;

In the query codeEscapedSearch should be escaped to handle the . in the code itself and the . in the regex.

Erwin Brandstetter · Accepted Answer · 2019-10-08 15:51:53Z

1

Consider this direct approach:

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$;

This does not return a query string but executes the UPDATE directly. Much shorter and faster.

Also using replace(), btw.

Thorough solution to escape all LIKE and regexp patterns:

Escape function for regular expression or LIKE patterns

edited Oct 8, 2019 at 15:51

answered Apr 2, 2015 at 12:15

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Nandakumar V Over a year ago

Thanks for the answer. I decided to assign a new variable because I need to repeat it in the next two operations too (edit/delete).

Nandakumar V Over a year ago

Also I put `\` thinking that the slash needed to be escaped. Any way thanks for your input.

Erwin Brandstetter Over a year ago

@NandakumarV: You need to be aware that plpgsql is different from other programming languages. Assignments are comparatively expensive. Do as much as possible in SQL statements.

Erwin Brandstetter Over a year ago

@NandakumarV: I adapted the function with variables to allow reuse of the patterns.

Nandakumar V Over a year ago

so saving data into a sql query will be faster than assigning it to a variable.. Thanks for that info.

Nandakumar V · Accepted Answer · 2015-04-02 12:06:34Z

0

Since no proper solution is identified, I decided to use simple replace
codeEscapedSearch = replace(codeSearch,'.','\\.');

answered Apr 2, 2015 at 12:06

Nandakumar V

4,7255 gold badges33 silver badges50 bronze badges

1 Comment

Erwin Brandstetter Over a year ago

replace() is ok, but not need for \\, just \. I am going to suggest a radically different function.

Collectives™ on Stack Overflow

Escaping a LIKE pattern or regexp string in Postgres 8.4 inside a stored procedure

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related