Skip site navigation (1) Skip section navigation (2)

Re: sub query constraint

From: Dale Sykora <dalen(at)czexan(dot)net>
To: Yudie Pg <yudiepg(at)gmail(dot)com>
Cc: Bruno Wolff III <bruno(at)wolff(dot)to>, pgsql-general(at)postgresql(dot)org
Subject: Re: sub query constraint
Date: 2005-03-29 17:00:23
Message-ID: 424989A7.3000800@czexan.net (view raw or flat)
Thread:
Lists: pgsql-general
Yudie Pg wrote:
>>One way to do this is to add a write_access column to actions and use
>>a constraint to force it to be true. 
>>Create a UNIQUE key of
>>(name, write_access) for user_data and then add a FOREIGN KEY
>>reference from (name, write_access) in actions to (name, write_access)
>>in user_data.
> 
> 
> 
> Yes the name must unique indexed but couldn't force the write_access
> to always 'true'.
> I may suggest create a trigger function to validate insert to table actions:
> 
> CREATE OR REPLACE FUNCTION validate_actions_insert() RETRUNS OPAQUE AS '
> DECLARE
> rs RECORD;
> 
> BEGIN
>   SELECT INTO rs * FROM user_data WHERE name = NEW.user and write_access = 't';
>    
>   IF NOT FOUND THEN
>      RAISE EXCEPTION ''writing access forbidden for user '', NEW.user;
>   END IF;
> 
>   RETURN NEW;
> END;
> ' LANGUAGE plpgsql;
> 
> CREATE TRIGGER tg_actions BEFORE INSERT OR UPDATE ON actions
> FOR EACH ROW EXECUTE PROCEDURE validate_actions_insert();
> 
> You may need create another trigger for table user_data before update
> for reverse validation.
> 
Bruno and Yudie,
	Thanks for the replies.  I will read up on triggers and give that a try.

Thanks,

Dale


In response to

pgsql-general by date

Next:From: Ragnar HafstaĆ°Date: 2005-03-29 17:16:33
Subject: Re: Views!
Previous:From: Devrim GUNDUZDate: 2005-03-29 16:51:43
Subject: Re: 8.0.2beta1 RPMs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group