Hi
My names can contain a special character (⠒), e.g. to separate the
donator object from the name or the name from the type. Typically, I use
this with foreign keys, e.g. table PARENT has the surrogate key ID, the
table CHILD would then have the column PARENT⠒ID. That way, I can use
the underscore to segment column names like yet_another_column_name. I
do not like camel-case in that case because names do not have to be case
sensitive.
However, I want to create a (trigger) function to impose data
consistency. For that purpose, I try to copy the data type of a PL/pgSQL
variable from the base object, a view in that case. Trying so, I get the
following error on installation of the function.
V⠒NODE_TYPE⠒NAME NODE⠒V.NODE_TYPE⠒NAME%type := null;
Syntax error at "%" … invalid type name. If I use the actual type of the
column, all is fine.
V⠒NODE_TYPE⠒NAME text;
Please find attached script files of objects directly involved in the
trigger function.
Is there something, I am doing wrongly?
Kind Regards
Thiemo
@echo Set up function NODE_GOOD⠒TR_B_IU_R;
set search_path = snowrunner, public;
create or replace function NODE_GOOD⠒TR_B_IU_R()
returns trigger
language plpgsql
stable as
$body$
declare
-- V⠒NODE_TYPE⠒NAME text;
V⠒NODE_TYPE⠒NAME NODE⠒V.NODE_TYPE⠒NAME%type := null;
begin
select NODE_TYPE⠒NAME into V⠒NODE_TYPE⠒NAME
from NODE⠒V
where 1 = 1
and ID = new.NODE⠒ID
and 1 = 1;
if ( V⠒NODE_TYPE⠒NAME = 'Drop-off'
and new.TASK_NAME is null) then
raise exception 'A good connection to a drop-off node must have a task name!';
elsif ( V⠒NODE_TYPE⠒NAME != 'Drop-off'
and new.TASK_NAME is not null) then
raise exception 'A good connection to a non-drop-off node cannot have a task name!';
end if;
return null;
end;
$body$;
commit;
@echo Set view NODE⠒V up;
create or replace view NODE⠒V as
select N.NAME as NODE⠒NAME,
-- N.QUANTITY,
T.NAME as NODE_TYPE⠒NAME,
M.MAP⠒NAME,
M.MAP⠒CODE,
M.REGION⠒NAME,
M.REGION⠒COMPLETION_PERCENTATGE,
A.CENTRICITY⠒NAME,
A.DIRECTION⠒CODE,
N.DESCRIPTION as NODE⠒DESCRIPTION,
T.DESCRIPTION as NODE_TYPE⠒DESCRIPTION,
M.MAP⠒DESCRIPTION,
M.REGION⠒DESCRIPTION,
A.AREA⠒DESCRIPTION,
A.CENTRICITY⠒DESCRIPTION,
A.DIRECTION⠒DESCRIPTION,
N.ID,
N.NODE_TYPE⠒ID,
N.MAP⠒ID,
M.REGION⠒ID,
N.AREA⠒ID,
A.CENTRICITY⠒ID,
A.DIRECTION⠒ID
from NODE N
inner join NODE_TYPE T
on N.NODE_TYPE⠒ID = T.ID
inner join MAP⠒V M
on N.MAP⠒ID = M.ID
inner join AREA⠒V A
on N.AREA⠒ID = A.ID;
create or replace rule NODE⠒R_I as
on insert
to NODE⠒V
do instead
insert into NODE (MAP⠒ID,
AREA⠒ID,
NODE_TYPE⠒ID,
NAME,
-- QUANTITY,
DESCRIPTION)
values (/* MAP⠒I */ case
-- ID given
when new.MAP⠒ID is not null then new.MAP⠒ID
-- name or code and region given
when ( new.MAP⠒CODE is not null
or new.MAP⠒NAME is not null)
and ( new.REGION⠒ID is not null
or new.REGION⠒NAME is not null) then (select ID
from MAP⠒V
where ( MAP⠒CODE = new.MAP⠒CODE
or MAP⠒NAME = new.MAP⠒NAME)
and ( REGION⠒ID = new.REGION⠒ID
or REGION⠒NAME = new.REGION⠒NAME))
else null
end,
/* AREA⠒ID */ case
-- ID given
when new.AREA⠒ID is not null then new.AREA⠒ID
-- name given
when ( new.DIRECTION⠒CODE is not null
or new.DIRECTION⠒ID is not null)
and ( new.CENTRICITY⠒ID is not null
or new.CENTRICITY⠒NAME is not null) then (select ID
from AREA⠒V
where ( DIRECTION⠒CODE = new.DIRECTION⠒CODE
or DIRECTION⠒ID = new.DIRECTION⠒ID)
and ( CENTRICITY⠒ID = new.CENTRICITY⠒ID
or CENTRICITY⠒NAME = new.CENTRICITY⠒NAME))
else null
end,
/* NODE_TYPE⠒ID */ case
-- ID given
when new.NODE_TYPE⠒ID is not null then new.NODE_TYPE⠒ID
-- name given
when new.NODE_TYPE⠒NAME is not null then (select ID
from NODE_TYPE
where NAME = new.NODE_TYPE⠒NAME)
else null
end,
/* NAME */ new.NODE⠒NAME,
-- /* QUANTITY */ new.QUANTITY,
/* DESCRIPTION */ new.NODE⠒DESCRIPTION);
comment on rule NODE⠒R_I on NODE⠒V is
'We do not support filling the joined tables, thus they have to be filled already; i.e. no recursion';
create or replace trigger NODE⠒V⠒TR_B_DU_S
before delete or update
on NODE⠒V
for each statement
execute function REJECT_OPERATION();
comment on view NODE⠒V is
'
$Header: svn+ssh://thiemo__sourceforge/p/snowrunner/code/trunk/code/postgresql/views/NODE%E2%A0%92V.pg_sql 2 2024-02-24 23:50:08Z thiemo $';
commit;
@echo Set view NODE_GOOD⠒V up;
create or replace view NODE_GOOD⠒V as
select N.NODE⠒NAME,
-- N.QUANTITY as QUANTITY_AVAILABLE_AT_NODE,
G.GOOD⠒NAME,
NG.QUANTITY,
NG.TASK_NAME,
N.CENTRICITY⠒NAME,
N.DIRECTION⠒CODE,
N.NODE_TYPE⠒NAME,
N.MAP⠒NAME,
N.MAP⠒CODE,
N.REGION⠒NAME,
G.GOOD_CLASS⠒NAME,
G.SIZE,
G.WEIGHT,
NG.DESCRIPTION as NODE_GOOD⠒DESCRIPTION,
N.NODE⠒DESCRIPTION,
N.NODE_TYPE⠒DESCRIPTION,
N.CENTRICITY⠒DESCRIPTION,
N.DIRECTION⠒DESCRIPTION,
N.AREA⠒DESCRIPTION,
N.MAP⠒DESCRIPTION,
N.REGION⠒DESCRIPTION,
G.GOOD⠒DESCRIPTION,
G.GOOD_CLASS⠒DESCRIPTION,
NG.ID,
NG.NODE⠒ID,
NG.GOOD⠒ID,
N.NODE_TYPE⠒ID,
N.CENTRICITY⠒ID,
N.DIRECTION⠒ID,
N.AREA⠒ID,
N.MAP⠒ID,
N.REGION⠒ID,
G.GOOD_CLASS⠒ID
from NODE_GOOD NG
inner join NODE⠒V N
on NG.NODE⠒ID = N.ID
inner join GOOD⠒V G
on NG.GOOD⠒ID = G.ID;
create or replace rule NODE_GOOD⠒R_I as
on insert
to NODE_GOOD⠒V
do instead
insert into NODE_GOOD (NODE⠒ID,
GOOD⠒ID,
QUANTITY,
TASK_NAME,
DESCRIPTION)
values (/* NODE⠒ID */ case
-- ID given
when new.NODE⠒ID is not null then new.NODE⠒ID
-- names or codes given
when new.NODE⠒NAME is not null
and ( new.MAP⠒NAME is not null
or new.MAP⠒CODE is not null)
and new.REGION⠒NAME is not null then (select ID
from NODE⠒V
where NODE⠒NAME = new.NODE⠒NAME
and NODE_TYPE⠒NAME = 'Crafting zone'
and ( MAP⠒NAME = new.MAP⠒NAME
or MAP⠒CODE = new.MAP⠒CODE)
and REGION⠒NAME = new.REGION⠒NAME)
else null
end,
/* GOOD⠒ID */ case
-- ID given
when new.GOOD⠒ID is not null then new.GOOD⠒ID
-- name given
when new.GOOD⠒NAME is not null then (select ID
from GOOD⠒V
where GOOD⠒NAME = new.GOOD⠒NAME)
else null
end,
/* QUANTITY */ new.QUANTITY,
/* TASK_NAME */ new.TASK_NAME,
/* DESCRIPTION */ new.NODE_GOOD⠒DESCRIPTION);
comment on rule NODE_GOOD⠒R_I on NODE_GOOD⠒V is
'We do not support filling the joined tables, thus they have to be filled already; i.e. no recursion';
create or replace trigger NODE_GOOD⠒V⠒TR_B_DU_S
before delete or update
on NODE_GOOD⠒V
for each statement
execute function REJECT_OPERATION();
comment on column NODE_GOOD⠒V.QUANTITY is
'Number of the good that initially can be picked up or must be dropped off depending on the node type. For crafting zones it is a pick-up number.';
comment on view NODE_GOOD⠒V is
'
$Header: svn+ssh://thiemo__sourceforge/p/snowrunner/code/trunk/code/postgresql/views/NODE_GOOD%E2%A0%92V.pg_sql 2 2024-02-24 23:50:08Z thiemo $';
commit;
