Thread: locale operation to be invoked, but no collation was derived (in trigger)

Hi,

first i am not sure how the state of the collation work in current git is supposed to be with all the discussion going
onhere... but wanted to get out that bug report:
 


create table ad_tab (ad_tab_id varchar(32), name varchar(32));

create function test_trg() RETURNS TRIGGER LANGUAGE plpgsql AS $function$ DECLAREBEGIN   --Check tab name starts with a
uppercase letter   IF (not (substr(new.Name,1,1) between 'A' and 'Z')) THEN     RAISE EXCEPTION '%',
'@TabName1stCharUpper@'; --OBTG:-20000--   END IF;END
 
; $function$
;

create trigger test_trg after update on test for each row execute procedure test_trg();

insert into test values ('1', 'test');
update test set name = 'test2' where test_id = '1';

ERROR:  locale operation to be invoked, but no collation was derived
CONTEXT:  PL/pgSQL function "test_trg" line 4 at IF

Regards,
Stefan

Stefan Huehner <stefan@huehner.org> writes:
> first i am not sure how the state of the collation work in current git is supposed to be with all the discussion
goingon here... but wanted to get out that bug report:
 

I think the current state is "plpgsql is about completely broken for
collation operations" :-(.  But thanks for the test case.
        regards, tom lane

Stefan Huehner <stefan@huehner.org> writes:
> first i am not sure how the state of the collation work in current git is supposed to be with all the discussion
goingon here... but wanted to get out that bug report:
 


> create table ad_tab (ad_tab_id varchar(32), name varchar(32));

> create function test_trg() RETURNS TRIGGER LANGUAGE plpgsql AS $function$ DECLARE
>  BEGIN
>     --Check tab name starts with a upper case letter
>     IF (not (substr(new.Name,1,1) between 'A' and 'Z')) THEN
>       RAISE EXCEPTION '%', '@TabName1stCharUpper@' ; --OBTG:-20000--
>     END IF;
>  END
> ; $function$
> ;

> create trigger test_trg after update on test for each row execute procedure test_trg();

> insert into test values ('1', 'test');
> update test set name = 'test2' where test_id = '1';

> ERROR:  locale operation to be invoked, but no collation was derived
> CONTEXT:  PL/pgSQL function "test_trg" line 4 at IF

Fixed, thanks for the test case.  (I have a feeling I'd better grep the
whole backend for other places that are likewise neglecting to set the
collation fields in manually-created expression nodes ...)
        regards, tom lane