#1
  1. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18

    Rules, Views and Updating multiple tables


    Ok, I have a view that joins two tables and I want to be able to insert on that view that will insert into both tables.

    Here's my current rule:
    PHP Code:
    CREATE RULE userinfo_ins AS ON INSERT TO userinfo
        
    DO INSTEAD
        INSERT INTO users VALUES 
    (NEW.emp_id, NEW.password, NEW.fname, NEW.mi, NEW.lname, NEW.emp_ssa, NEW.emp_status
    This first rule seems to work well, however I'd also like to bundle the following query with it:
    PHP Code:
    INSERT INTO signon VALUES (NEW.emp_id, NEW.signon_id
    I just don't know what the syntax is. In the manual it says the syntax is:

    CREATE RULE rule_name AS ON event
    TO object [WHERE rule_qualification]
    DO [INSTEAD] [action | (actions) | NOTHING];

    but the sample only seems to show 1 action being taken. I'm sure I'm just missing something simple. Also, is there a way I can use some sort of CASE where if NEW.signon_id is null I don't do the insert to the second table? If I'm missing any pertinent info, just let me know and I'll post it.
    Here's my setup:
    RH 7.2
    Postgres 7.1.3 (yes I'm in dire need of an upgrade)
    phpPGAdmin 2.4a (btw, is there functionality in phpPGAdmin to view rules)

    UPDATE
    Apparently the problem I was having was with phpPGAdmin when trying to create the rule it halts on the first semi-colon when doing mult-action rules. This rule works when I enter it in psql
    PHP Code:
    CREATE RULE userinfo_ins AS ON INSERT TO userinfo
        
    DO INSTEAD    
        
    (INSERT INTO users VALUES (NEW.emp_id, NEW.password, NEW.fname, NEW.mi, NEW.lname, NEW.emp_ssa, NEW.emp_status);       
        
    INSERT INTO signon VALUES (NEW.emp_id, NEW.signon_id); ); 
    Any ideas regarding my case question or phppgadmin question about viewing rules?
    Thanks,
    -b
    Last edited by bcyde; April 11th, 2003 at 04:48 PM.
    PostgreSQL, it's what's for dinner...
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Hey bcyde,

    Congratulations . You figured out how to add as many SQL statements as you want to your RULE. Yes, i agree that its frustrating that the documentation doesn't show an example of this. I suppose I should post an example in the contrib notes underneath. (makes mental note...)

    Anyway, i don't know if you can have conditional execution of multiple queries, but have you even tried adding the CASE logic?

    What about
    Code:
    CREATE RULE userinfo_ins AS ON INSERT TO userinfo
    
        DO INSTEAD    
    
        (INSERT INTO users VALUES (NEW.emp_id, NEW.password, NEW.fname, NEW.mi, NEW.lname, NEW.emp_ssa, NEW.emp_status);       
    
        (CASE WHEN condition='met' THEN
    
            INSERT INTO signon VALUES (NEW.emp_id, NEW.signon_id);
    
        END; )
    
     );
    (not sure of the correct syntax without testing, but see if something like this will work.)

    If this doesn't work, remember, there is always another way to do things. The most obvious solution is to just write a stored procedure, and create a trigger, thus forgetting about the rule completely. Procedures can be written in a completely procedural language (heh), thus allowing you more freedom in some areas. But, with a little more exploration of SQL and the RULE system, I have a feeling it could be handled completely in a rule.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    High above the mucky-muck (Columbus, OH)
    Posts
    266
    Rep Power
    14
    Originally posted by rycamor
    But, with a little more exploration of SQL and the RULE system, I have a feeling it could be handled completely in a rule.
    It *should* be handled invisibly by the DBMS. That is to say the view should be updateable/insertable. Can PostgreSQL do that? If not, do they have any plans to do so?
  6. #4
  7. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Thanks rycamor,
    I've tried different variants of the CASE statment but the parser always seems to not like it. I'll give it a few more shots and if I can't get it I'll probably just fire the question off to the pg mailing list.

    Regarding using a trigger instead, I was under the impression (probably false) that in order to insert into a view composed of multiple tables a rule was required. I'm not exactly sure how postgres sees the relationship between views/triggers (I know that in the docs it says that views are just implemented as rules), but will postgres be able to "see" the trigger and allow insertion into the view?

    Thanks for your help... again

    -b
    PostgreSQL, it's what's for dinner...
  8. #5
  9. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Originally posted by MattR
    It *should* be handled invisibly by the DBMS. That is to say the view should be updateable/insertable. Can PostgreSQL do that? If not, do they have any plans to do so?
    Please explain to me how a standard view-updating system, such as in Sybase, Oracle, etc... would handle extra logic such as bcyde is trying to handle. In other words, this is not just a standard view-updating operation, AFAIK.

    But anyway, yes, PostgreSQL does not automatically provide view updating, but the RULE system is an extremely easy way to handle view updating, even though you do it "by hand". The benefit is that you get to make it do whatever tricks you want. This can be an advantage in certain cases, because view updating can be ambiguous at times, especially because the DBMS may not be able to know exactly what you want.

    The next version of PostgreSQL will be automatically creating update rules for views, but then the DBA will be able to modify them. Sounds like the best of both worlds to me...
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    High above the mucky-muck (Columbus, OH)
    Posts
    266
    Rep Power
    14
    Originally posted by rycamor
    Please explain to me how a standard view-updating system, such as in Sybase, Oracle, etc... would handle extra logic such as bcyde is trying to handle. In other words, this is not just a standard view-updating operation, AFAIK.
    Unless you use a trigger, they can't either (although Oracle's materialized views may be able to filter down).

    View updates are view updates.

    The next version of PostgreSQL will be automatically creating update rules for views, but then the DBA will be able to modify them. Sounds like the best of both worlds to me... [/B]
    Sounds about right. Although again view updating is not an impossible task, just a difficult one, and if any DBMS wanted to spend a lot of time and money they could do it correctly. Triggers are probably the 'next best thing', it's too bad though it would make everyone's lives easier.
  12. #7
  13. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    For future reference for anyone, here's what was recommended to by Tom Lane from the postgres mailing list:
    CASE is not the solution. Do something like
    INSERT INTO target SELECT this, that, theother WHERE condition;
    And here's what works for me:
    PHP Code:
    CREATE RULE userinfo_ins AS ON INSERT TO userinfo
        
    DO INSTEAD    
        
    (INSERT INTO users VALUES (NEW.emp_id, NEW.password, NEW.fname, NEW.mi, NEW.lname, NEW.emp_ssa, NEW.emp_status);       
        
    INSERT INTO signon (SELECT NEW.emp_id, NEW.signon_id WHERE NEW.signon_id IS NOT NULL); ); 
    Thanks rycamor and MattR for your input,
    -b
    PostgreSQL, it's what's for dinner...

IMN logo majestic logo threadwatch logo seochat tools logo