regex in postgresql function

Question

I need to remove (by means of a function) possible non-latin characters (chinese, japanese, ...) by means of a regex expression from a Postgres database table.

I have tried all solutions I could find online, but nothing seems to work.

CREATE OR REPLACE FUNCTION public.function_104(param text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN  
    EXECUTE 'UPDATE public.' || quote_ident(param) || ' SET "name" = REGEXP_REPLACE("name", [^x00-x7F]+, " ")';

END
$function$

I keep running into following error message :

psycopg2.errors.SyntaxError: syntax error at or near "["
LINE 1: ..._roads_free_1 SET "name" = REGEXP_REPLACE("name", [^x00-x7F]...
                                                             ^
QUERY:  UPDATE public.gis_osm_roads_free_1 SET "name" = REGEXP_REPLACE("name", [^x00-x7F]+, " ")
CONTEXT:  PL/pgSQL function afri_terra_104(text) line 6 at EXECUTE

```

I tried similar solutions : they always return similar errors — EricBHK
– EricBHK, Commented Oct 22, 2021 at 20:24
regardless of the regex expression validity, it must be put between single quotes. Same thing for the replacement value — JGH
– JGH, Commented Oct 22, 2021 at 20:24
in my case, I am only dealing with non-latin streetnames (= non-latin letters), but removing other non-ASCII punctuations and symbols okay — EricBHK
– EricBHK, Commented Oct 22, 2021 at 20:27
I put it between singles quotes, which used to work jut fine until I replaced the hard-coded datbase name by the param value. from that momnt on, the '[' is causing issues — EricBHK
– EricBHK, Commented Oct 22, 2021 at 20:31

JGH · Accepted Answer · 2021-10-22 20:31:24Z

1

You must put the regex between single quotes, as well as the replacement text. Since it is a dynamic query, you must escape the single quotes by doubling them:

CREATE OR REPLACE FUNCTION public.function_104(param text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN  
    EXECUTE 'UPDATE public.' || quote_ident(param) || 
           ' SET "name" = REGEXP_REPLACE("name", ''[^x00-x7F]+'', '' '')';

END
$function$;


insert into t104(name) values('abcé');
INSERT 0 1

select function_104('t104');
 function_104
--------------

(1 row)

select * from t104;
 name
------
 abc
(1 row)

answered Oct 22, 2021 at 20:31

JGH

18.2k5 gold badges40 silver badges57 bronze badges

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

Collectives™ on Stack Overflow

regex in postgresql function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related