Thread: Weird "could not determine which collation to use for stringcomparison" with LEAST/GREATEST on PG11 procedure
Weird "could not determine which collation to use for stringcomparison" with LEAST/GREATEST on PG11 procedure
Hello,
When I run the following script on a PG 11 psql:
select version();
show lc_collate;
create or replace function same_values_func(a text, b text) returns void as $body$
begin
assert a = b;
end;$body$ language plpgsql;
select same_values_func(least('a','b'),'a');
create or replace procedure same_values_proc(a text, b text) as $body$
begin
assert a = b;
end;$body$ language plpgsql;
call same_values_proc(least('a','b'),'a');
I get the following output and error at the end:
SIMPLE=> select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 11.0 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit
(1 row)
SIMPLE=>
SIMPLE=> show lc_collate;
lc_collate
------------
C
(1 row)
SIMPLE=>
SIMPLE=> create or replace function same_values_func(a text, b text) returns void as $body$
SIMPLE$> begin
SIMPLE$> assert a = b;
SIMPLE$> end;$body$ language plpgsql;
CREATE FUNCTION
SIMPLE=>
SIMPLE=> select same_values_func(least('a','b'),'a');
same_values_func
------------------
(1 row)
SIMPLE=>
SIMPLE=> create or replace procedure same_values_proc(a text, b text) as $body$
SIMPLE$> begin
SIMPLE$> assert a = b;
SIMPLE$> end;$body$ language plpgsql;
CREATE PROCEDURE
SIMPLE=>
SIMPLE=> call same_values_proc(least('a','b'),'a');
ERROR: could not determine which collation to use for string comparison
HINT: Use the COLLATE clause to set the collation explicitly.
SIMPLE=>
I tried to convert all void functions we have on a PG10 DB to procedures and I get this strange error.
The same error occurs with GREATEST.
Any idea?
Regards.
This email was sent to you by Moody’s Investors Service EMEA Limited
Registered office address:
One Canada Square
Canary Wharf
London, E14 5FA
Registered in England and Wales No: 8922701