Re: repost of how to do select in a constraint - Mailing list pgsql-general

Dennis Gearon wrote:

> I'd like to create a table constraint, a trigger, a rule, whatever that
> would
> prevent insertion or updates of a row that had any other type besides:
>
> 'Group'
>
> Into the table 'MeetGrpDescs'. I don't want to hardcode in the 'org_id'
> value,
> I'd like to put something like a join on org_type_id and then test if
> org_type = 'Group'.

Check constraints apparently cannot contain subselects, but they can contain
function calls, so how about something like:

CREATE FUNCTION org_type(int4) RETURNS text AS '
  select org_type from OrgTypes where org_type_id = $1;
' LANGUAGE sql;

ALTER TABLE MeetGrpDescs
  ADD CONSTRAINT GroupsOnly CHECK(org_type(org_type_id) = 'Group');

--
Peter Gibbs
EmKel Systems