Reputation: 1
Environment:
A simple example of the problem:
CREATE OR REPLACE FUNCTION public.test()
RETURNS TEXT
LANGUAGE plpython3u
AS $function$
tmp = plpy.execute("SELECT field FROM table WHERE filter_pointing_at_a_single_row;")[0]['field']
plpy.execute("UPDATE table SET field='"+tmp+"' WHERE filter_pointing_at_a_single_row;")
$function$;
When the content is 'łódź', running this once results in it being changed to 'Ĺ‚ĂłdĹş'.
We have tens if not hundreds of functions that perform operations like that. The original solution used python2 with default encoding changed to iso8859-2, but it's time to upgrade and such trick won't work in python3.
Other observations:
CREATE OR REPLACE FUNCTION public.test()
RETURNS TEXT
LANGUAGE plpython3u
AS $function$
tmp = plpy.execute("SELECT field FROM table WHERE filter_pointing_at_a_single_row;")[0]['field']
ret = plpy.execute("SELECT '"+tmp+"' AS \"A\" ;")
return ret[0]['A']
$function$;
In psql with correct client-encoding as well as in DBeaver which forces utf-8 it returns 'Ĺ‚ĂłdĹş'.
Meanwhile:
CREATE OR REPLACE FUNCTION public.test()
RETURNS TEXT
LANGUAGE plpython3u
AS $function$
tmp = plpy.execute("SELECT field FROM table WHERE filter_pointing_at_a_single_row;")[0]['field']
return tmp
$function$;
Returns 'łódź' both in DBeaver and psql.
Edit: I didn't mention what I need. We're looking for the best solution. We're considering moving the database to UTF-8, or rewriting all the functions that need it if we find other solution. There's also hope that some smart fix exists that would minimise the amount of work needed to solve this.
Upvotes: 0
Views: 161