I'm trying to write a trigger that enforces a constraint which can't
be done via an index. Namely, I have 4 columns which, in combination,
should be unique. Two of those columns can have null entries in which
case, the row with the null entry should be unique when considering
the other non-null parts.
I'm running into a couple of problems. The plpgsql parser doesn't
seem to like my trigger function since it keeps complaining when it
tries to compile it. In order to debug, I simplified the trigger to
the following:
create function deepsky_nodups() returns opaque as '
begin
if (NEW.suffix is NULL and NEW.component is NULL) then
if (exists select id from deepsky where catalog = NEW.catalog and entry = NEW.entry and suffix is null and
componentis null) then
return NULL;
end if;
end if;
end;
' language 'plpgsql';
where the table deepsky is defined as
create table deepsky (
id serial primary key,
catalog varchar (10) not null,
entry varchar (30) not null,
suffix varchar (1), -- NGC/IC catalogues use this
component varchar (1), -- NGC/IC catalogues use this
status varchar (2),
mag_p numeric (6,2),
type varchar (10),
surface_brightness numeric (6,2),
constellation varchar (3),
ra numeric,
decl numeric,
epoch varchar (10),
diameter numeric (8,2),
diameter_b numeric (8,2),
position_angle numeric (6,2),
remark text
);
postgresql doesn't like the "if (exists select id from ...)".
How do I need to form this query to check for an existing entry?
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
