The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Databases
> PostgreSQL Help
|
Rules, Views and Updating multiple tables
Discuss Rules, Views and Updating multiple tables in the PostgreSQL Help forum on Dev Shed.
Rules, Views and Updating multiple tables PostgreSQL Help forum discussing administration, SQL syntax, or other PostgreSQL-related topics. PostgreSQL provides enterprise level database functionality at open source prices.
|
|
 |
|
|
|
|
Dev Shed Forums Sponsor:
|
|
|
April 11th, 2003, 04:00 PM
|
 |
Me likey breadsticks...
|
|
Join Date: Jan 2003
Location: Los Angeles
|
|
|
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
__________________
PostgreSQL, it's what's for dinner...
Last edited by bcyde : April 11th, 2003 at 04:48 PM.
|
April 14th, 2003, 01:03 PM
|
|
Gödelian monster
|
|
Join Date: Jul 1999
Location: Central Florida, USA
|
|
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.
|
April 14th, 2003, 01:43 PM
|
|
Contributing User
|
|
Join Date: Jun 2001
Location: High above the mucky-muck (Columbus, OH)
Posts: 266
Time spent in forums: < 1 sec
Reputation Power: 12
|
|
Quote: 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?
|
April 14th, 2003, 03:58 PM
|
|
Me likey breadsticks...
|
|
Join Date: Jan 2003
Location: Los Angeles
|
|
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
|
April 14th, 2003, 05:17 PM
|
|
Gödelian monster
|
|
Join Date: Jul 1999
Location: Central Florida, USA
|
|
Quote: 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...
|
April 14th, 2003, 08:17 PM
|
|
Contributing User
|
|
Join Date: Jun 2001
Location: High above the mucky-muck (Columbus, OH)
Posts: 266
Time spent in forums: < 1 sec
Reputation Power: 12
|
|
Quote: 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.
Quote: |
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. 
|
April 15th, 2003, 09:04 AM
|
|
Me likey breadsticks...
|
|
Join Date: Jan 2003
Location: Los Angeles
|
|
For future reference for anyone, here's what was recommended to by Tom Lane from the postgres mailing list:
Quote:
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
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
 Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
| 
