Re: [SQL] Proper case function
Michael Gould wrote: Gary, Based on what I read it wouldn't handle cases where the result should be MacDonald from macdonald. There are other cases such as the sentence below I've looked at rationalising names in this manner before, and found that, depending on the individual, both Macdonald and Macdonald are correct. So I waved my hands around, harrumphed and went for Macdonald in all cases. I'm sure that should the MacDonalds have not become accustomed to being recorded as Macdonalds, surely they will soon, or at least not complain about it. -- -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Fwd: conditional rule not applied
Hi, Apologies for posting this from postgresql.general, but this failed to get any follow-ups in that NG. Hopefully someone here can shed some light on this. --- Begin Message --- Topics: conditional rule not applied Re: conditional rule not applied Re: conditional rule not applied Re: conditional rule not applied Re: conditional rule not applied --- End Message --- --- Begin Message --- Hi, I'm trying to create a rule to be applied on update to a view that consists of two joined tables. Table 'shoes' below is left-joined with table 'shoelaces' in the view 'footwear'. I'd like to create a simple update rule on the view, only if the value of a common column corresponds to an inexistent record in 'shoelaces', so the result is an INSERT into 'shoelaces' with the new record: ------ CREATE TABLE shoes ( sh_id serial PRIMARY KEY, sh_name text, sh_avail integer ); CREATE TABLE shoelaces ( sl_id serial PRIMARY KEY, sh_id integer REFERENCES shoes, sl_name text ); INSERT INTO shoes (sh_name, sh_avail) VALUES ('sh1', 2), ('sh2', 0), ('sh3', 4), ('sh4', 3); INSERT INTO shoelaces (sh_id, sl_name) VALUES (1, 'sl1'), (3, 'sl2'); SELECT * FROM shoes; sh_id | sh_name | sh_avail ---+-+-- 1 | sh1 |2 2 | sh2 |0 3 | sh3 |4 4 | sh4 |3 SELECT * FROM shoelaces; sl_id | sh_id | sl_name ---+---+- 1 | 1 | sl1 2 | 3 | sl2 (2 rows) CREATE VIEW footwear AS SELECT sh.sh_id, sh_name, sh_avail, sl_name FROM shoes sh LEFT JOIN shoelaces sl USING (sh_id); SELECT * FROM footwear; sh_id | sh_name | sh_avail | sl_name ---+-+--+- 1 | sh1 |2 | sl1 2 | sh2 |0 | 3 | sh3 |4 | sl2 4 | sh4 |3 | (4 rows) CREATE RULE footwear_nothing_upd AS ON UPDATE TO footwear DO INSTEAD NOTHING; CREATE RULE footwear_newshoelaces_upd AS ON UPDATE TO footwear WHERE NEW.sl_name <> OLD.sl_name AND OLD.sl_name IS NULL DO INSERT INTO shoelaces (sh_id, sl_name) VALUES(NEW.sh_id, NEW.sl_name); -- Testing: result should be a new record in 'shoelaces' UPDATE footwear SET sl_name = 'sl3' WHERE sh_name = 'sh2'; -- but that doesn't happen: SELECT * FROM shoelaces; sl_id | sh_id | sl_name ---+---+- 1 | 1 | sl1 2 | 3 | sl2 (2 rows) --- --- Any tips would be much appreciated. -- Seb -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general --- End Message --- --- Begin Message --- On Wed, 30 Dec 2009 19:39:15 -0600, Seb wrote: > CREATE RULE footwear_nothing_upd AS > ON UPDATE TO footwear DO INSTEAD NOTHING; > CREATE RULE footwear_newshoelaces_upd AS > ON UPDATE TO footwear > WHERE NEW.sl_name <> OLD.sl_name AND OLD.sl_name IS NULL > DO > INSERT INTO shoelaces (sh_id, sl_name) > VALUES(NEW.sh_id, NEW.sl_name); I think my error is in the test expression, which doesn't deal properly with the null value, so correcting: CREATE RULE footwear_nothing_upd AS ON UPDATE TO footwear DO INSTEAD NOTHING; CREATE RULE footwear_newshoelaces_upd AS ON UPDATE TO footwear WHERE NEW.sl_name IS DISTINCT FROM OLD.sl_name AND OLD.sl_name IS NULL DO INSERT INTO shoelaces (sh_id, sl_name) VALUES(NEW.sh_id, NEW.sl_name); However, could a more direct and robust test for an inexistent record in 'shoelaces' be made? -- Seb -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general --- End Message --- --- Begin Message --- On Wed, 30 Dec 2009 20:04:51 -0600, Seb wrote: > On Wed, 30 Dec 2009 19:39:15 -0600, > Seb wrote: > CREATE RULE footwear_nothing_upd AS >> ON UPDATE TO footwear DO INSTEAD NOTHING; CREATE RULE >> footwear_newshoelaces_upd AS ON UPDATE TO footwear WHERE NEW.sl_name >> <> OLD.sl_name AND OLD.sl_name IS NULL DO INSERT INTO shoelaces >> (sh_id, sl_name) VALUES(NEW.sh_id, NEW.sl_name); > I think my error is in the test expression, which doesn't deal > properly with the null value, so correcting: > CREATE RULE footwear_nothing_upd AS > ON UPDATE TO footwear DO INSTEAD NOTHING; > CREATE RULE footwear_newshoelaces_upd AS > ON UPDATE TO footwear > WHERE NEW.sl_name IS DISTINCT FROM OLD.sl_name AND OLD.sl_name IS NULL > DO > INSERT INTO shoelaces (sh_id, sl_name) > VALUES(NEW.sh_id, NEW.sl_name); > However, could a more direct and robust test for an inexistent record > in 'shoelaces' be made? Any ideas? I'm not sure this is the best way to test whether the record to update corresponds to a inexistent record in 'shoelac
Re: [SQL] Fwd: conditional rule not applied
On Wed, Jan 6, 2010 at 12:40 PM, Seb wrote: > I'm trying to create a rule to be applied on update to a view that > consists of two joined tables. Table 'shoes' below is left-joined with > table 'shoelaces' in the view 'footwear'. I'd like to create a simple > update rule on the view, only if the value of a common column > corresponds to an inexistent record in 'shoelaces', so the result is an > INSERT into 'shoelaces' with the new record: A couple of year's ago, I was seriously looking into update-able views. But from my experience, I'm sorry to say you not going to find a robust solution to this problem. There are at least three problems with joined table update-able views: 1) You can only issue insert-update-delete statements that will only affect one row. 2) You cannot serialize the update of a view's virtual row like you can with a table's row. This allow leave the possibility of concurrent update anomalies. 3) Application frameworks that use optimistic locking or use the updated row count for validation will complain (and automatically roll-back your work) when you attempt to perform an update. The official use for update-able views is for limiting the results from a *single* base table. Having said all of this, it is possible to do what your describing. I've seen Keith Larson make update-able views from a composite of selected UNION and FULL OUT JOIN queries. But his solution was extremely hackish. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Fwd: conditional rule not applied
On Wed, 6 Jan 2010 13:01:02 -0800, Richard Broersma wrote: > On Wed, Jan 6, 2010 at 12:40 PM, Seb wrote: >> I'm trying to create a rule to be applied on update to a view that >> consists of two joined tables. Table 'shoes' below is left-joined >> with table 'shoelaces' in the view 'footwear'. I'd like to create a >> simple update rule on the view, only if the value of a common column >> corresponds to an inexistent record in 'shoelaces', so the result is >> an INSERT into 'shoelaces' with the new record: > A couple of year's ago, I was seriously looking into update-able > views. But from my experience, I'm sorry to say you not going to find > a robust solution to this problem. There are at least three problems > with joined table update-able views: 1) You can only issue > insert-update-delete statements that will only affect one row. 2) You > cannot serialize the update of a view's virtual row like you can with > a table's row. This allow leave the possibility of concurrent update > anomalies. 3) Application frameworks that use optimistic locking or > use the updated row count for validation will complain (and > automatically roll-back your work) when you attempt to perform an > update. > The official use for update-able views is for limiting the results > from a *single* base table. > Having said all of this, it is possible to do what your describing. > I've seen Keith Larson make update-able views from a composite of > selected UNION and FULL OUT JOIN queries. But his solution was > extremely hackish. Thank you, Richard. So IIUC, this may not be problematic in my particular case of a single user database, where I have some control over concurrent operations, i.e. the possibility of those anomalies is minimal (or at least is under my control to a large extent). WRT item (1), in the example I showed (with the last rule), the following update appears to work correctly: UPDATE footwear SET sl_name='sl3' WHERE sh_name='sh2' OR sh_name='sh4'; where 2 tuples are inserted into shoelaces, as expected. Maybe you're referring to views with other types of joined tables? Do you think the NOT EXISTS statement in my last rule makes sense in the context of what I described? I'm not sure I'm following the docs on the rule system properly on how the NEW and OLD relations should be used, especially the apparent contradiction in the "condition" parameter. At any rate, I'm thankful for the warning about the limitations of updteable views. -- Seb -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
