As normal array types in pgsql are started at index 1, but suprisingly, the TG_ARGV input arg for triggers are zero indexed!
This is becasue the TG_ARGV were defined to be indexed from zero
Considering following example!
create table x(
_id serial,
_val int
)
create or replace function tgr_func0()returns trigger as $$
begin
if(new._val<(TG_ARGV[0]::int)) then /*yes, index starts from zero for TG_ARGV*/
raise exception 'Error, cannot add _val value less than %',(TG_ARGV[0]::int);
return null;
end if;
return new;
end
$$ language plpgsql;
--drop trigger _tgr_010 on x
create trigger _tgr_010 before insert on x for each row execute procedure tgr_func0(10);
insert into x(_val) values(5);
/*result:
ERROR: Error, cannot add _val value less than 10
CONTEXT: PL/pgSQL function tgr_func0() line 4 at RAISE
SQL state: P0001
*/For specifying a zero(or even negetive val!), the definition should be something like following
with c as (
select '[-1:0]={0,1}'::int[] as arr /*first index is -1 now!*/
)
select arr[0] from c /*returns 1*/
with c as (
select '[0:0]={0}'::int[] as arr /*normal zero-index array*/
)
select arr[0] from c /*returns 0*/links: