Re: repost of how to do select in a constraint

So, the constraint would only check what's being UPDATE'd or INSERT'd, so therefore only a
single value for org_type_id from the INSERT/UPDATE statement would be passed to the function,
correct?


1/20/2003 11:04:35 PM, "Peter Gibbs" <peter@emkel.co.za> wrote:

>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
>
>