regex in postgresql function-CodePudding

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

```

CodePudding user response：

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)