Thread: How to cascade information like the user roles ?

Hi,

I need something like the user-roles of PG to store options of my users.
I guess i need a table with roles, options and one that stores the 
refernces from roles to options.

roles (role_id, role_name)
option (option_id, option_name)
role_has_option (role_fk, option_fk)

so far is easy. Now I can let role1 have option1 and option2 ...

But I'd further like to let role2 inherit role1's options and also have 
option3.
role_inherits_role (parent_role_fk, child_role_fk)
1, 2

What SELECT would deliver all options for role2 inkluding the inherited 
ones?
like
role_id, option_id
2, 1
2, 2
2, 3

Filip Rembiałkowski schrieb:
>
>
> 2010/1/19 Andreas <maps.on@gmx.net <mailto:maps.on@gmx.net>>
>
>     Hi,
>
>     I need something like the user-roles of PG to store options of my
>     users.
>     I guess i need a table with roles, options and one that stores the
>     refernces from roles to options.
>
>     roles (role_id, role_name)
>     option (option_id, option_name)
>     role_has_option (role_fk, option_fk)
>
>     so far is easy. Now I can let role1 have option1 and option2 ...
>
>     But I'd further like to let role2 inherit role1's options and also
>     have option3.
>     role_inherits_role (parent_role_fk, child_role_fk)
>     1, 2
>
>     What SELECT would deliver all options for role2 inkluding the
>     inherited ones?
>     like
>     role_id, option_id
>     2, 1
>     2, 2
>     2, 3
>
>
>
> select role_fk as role_id, option_fk as option_id from role_has_option 
> where role_fk = 2
> union
> select inh.child_role_fk, opt.option_fk from role_has_option opt join 
> role_inherits_role inh on inh.parent_role_fk = opt.role_fk where 
> inh.child_role_fk = 2
Thanks.
I am looking for a more general solution that expands even multiple 
steps of inheritance like a more complex example:
role_1  -->  option_1 + option_2
role_2  -->  option_3 and inherits role_1
role_3  -->  option_2 + option_4

role_4  -->  option_5 and inherits role_2 and role_3

I need a general solution that gives all options for any given role 
including every inherited options over a unlimited hierarchy of parents.
Sounds complex, I know, but this is what PG does with its user-roles.
So I'd do in this example a SELECT ... WHERE role_id = 4
and get
4, 5                 directly
4, 3                 from role_2
4, 1                 from role_1 over role_2
4, 2                 from role_1 over role_2
4, 2                 from role_3 (inherited double occurance)
4, 4                 from role_4

Thanks a whole lot and some :)

It's great that you actually did a working script.
I find it tremendosly easier to learn with a working example than with 
some links to other documentation which makes or does not make sense.

I've got a 8.4 server so both ways work nicely.

Is there a way to prevent inheritance loops instead of denying  
parent_id > child_id ?
Parallel inheritance has to work, though.   1  <-- 2 and 3 <-- 4

I found the function running into a stack overflow when I intetionally 
created a loop to check what'll happen. At least this is a predefined 
limit in max_stack_depth.
The with recursive thingy just kept going which generally tends to be 
worse, I guess.


Regards :)

I am trying to create a update trigger on a table that basically will only fire when a specific column is updated.  I am using version 8.4.3.

 

My plan of attack was to always fire on any row update, and pass in the OLD and NEW column that I want to check.

 

CREATE TRIGGER check_lockout

    AFTER UPDATE ON acct_table

    FOR EACH ROW

    EXECUTE PROCEDURE acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);

 

 

This fails with :

 

[postgres@linux1505 ~]$ cat check_lockout_trig.sql | psql testdb

ERROR:  syntax error at or near "OLD"

LINE 4:     EXECUTE PROCEDURE acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);

 

 

What am I doing wrong? Or is there a better way to go about this?

                                          

 

 

CREATE TRIGGER check_lockout

    AFTER UPDATE ON acct_table

    FOR EACH ROW    EXECUTE PROCEDURE acct_unlock();


Next the trigger function would look something like this

create or replace function acct_unlock()
returns trigger as
$$

if  (OLD.userid <> NEW.password)
    do  something
end if;

$$


keep in mind the acct_unlock must be  returns trigger

Then return either NEW or the OLD record 
OLD if  not changing the record or NEW if  the updated values are to be stored in the table.



All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.

"Plugge, Joe R." <JRPlugge@west.com> writes:
> This is what I have and it seems to work:

>             IF OLD.password != NEW.password

It'd be better to write "IF OLD.password IS DISTINCT FROM NEW.password".
The way with != will not do what you want if one value is null and the
other isn't.  It's possible this doesn't matter in this particular case
(if password can't ever be null in this table), but being careful about
nulls is a good habit to cultivate.
        regards, tom lane