diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/utils/adt/ruleutils.c | 2 | ||||
| -rw-r--r-- | src/test/regress/expected/rules.out | 55 | ||||
| -rw-r--r-- | src/test/regress/sql/rules.sql | 23 |
3 files changed, 75 insertions, 5 deletions
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 8cdef086a0b..0585251d8fe 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -5500,7 +5500,7 @@ get_insert_query_def(Query *query, deparse_context *context) get_rule_expr(confl->arbiterWhere, context, false); } } - else + else if (confl->constraint != InvalidOid) { char *constraint = get_constraint_name(confl->constraint); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index a2ed8fa7fa7..60c1f408fcc 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2811,14 +2811,26 @@ CREATE TABLE hat_data ( ); create unique index hat_data_unique_idx on hat_data (hat_name COLLATE "C" bpchar_pattern_ops); --- okay +-- DO NOTHING with ON CONFLICT CREATE RULE hat_nosert AS ON INSERT TO hats DO INSTEAD INSERT INTO hat_data VALUES ( NEW.hat_name, NEW.hat_color) ON CONFLICT (hat_name COLLATE "C" bpchar_pattern_ops) WHERE hat_color = 'green' - DO NOTHING RETURNING *; + DO NOTHING + RETURNING *; +SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename; + definition +--------------------------------------------------------------------------------------------- + CREATE RULE hat_nosert AS + + ON INSERT TO hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) + + VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name COLLATE "C" bpchar_pattern_ops)+ + WHERE (hat_data.hat_color = 'green'::bpchar) DO NOTHING + + RETURNING hat_data.hat_name, + + hat_data.hat_color; +(1 row) + -- Works (projects row) INSERT INTO hats VALUES ('h7', 'black') RETURNING *; hat_name | hat_color @@ -2845,6 +2857,34 @@ SELECT tablename, rulename, definition FROM pg_rules (1 row) DROP RULE hat_nosert ON hats; +-- DO NOTHING without ON CONFLICT +CREATE RULE hat_nosert_all AS ON INSERT TO hats + DO INSTEAD + INSERT INTO hat_data VALUES ( + NEW.hat_name, + NEW.hat_color) + ON CONFLICT + DO NOTHING + RETURNING *; +SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename; + definition +------------------------------------------------------------------------------ + CREATE RULE hat_nosert_all AS + + ON INSERT TO hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color)+ + VALUES (new.hat_name, new.hat_color) ON CONFLICT DO NOTHING + + RETURNING hat_data.hat_name, + + hat_data.hat_color; +(1 row) + +DROP RULE hat_nosert_all ON hats; +-- Works (does nothing) +INSERT INTO hats VALUES ('h7', 'black') RETURNING *; + hat_name | hat_color +------------+------------ + h7 | black +(1 row) + +-- DO UPDATE with a WHERE clause CREATE RULE hat_upsert AS ON INSERT TO hats DO INSTEAD INSERT INTO hat_data VALUES ( @@ -2855,6 +2895,17 @@ CREATE RULE hat_upsert AS ON INSERT TO hats SET hat_name = hat_data.hat_name, hat_color = excluded.hat_color WHERE excluded.hat_color <> 'forbidden' RETURNING *; +SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename; + definition +----------------------------------------------------------------------------------------------------------------------------------------- + CREATE RULE hat_upsert AS + + ON INSERT TO hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) + + VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name) DO UPDATE SET hat_name = hat_data.hat_name, hat_color = excluded.hat_color+ + WHERE (excluded.hat_color <> 'forbidden'::bpchar) + + RETURNING hat_data.hat_name, + + hat_data.hat_color; +(1 row) + -- Works (does upsert) INSERT INTO hats VALUES ('h8', 'black') RETURNING *; hat_name | hat_color diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index 1f9f7e69d28..561e2fd29a5 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -1062,14 +1062,16 @@ CREATE TABLE hat_data ( create unique index hat_data_unique_idx on hat_data (hat_name COLLATE "C" bpchar_pattern_ops); --- okay +-- DO NOTHING with ON CONFLICT CREATE RULE hat_nosert AS ON INSERT TO hats DO INSTEAD INSERT INTO hat_data VALUES ( NEW.hat_name, NEW.hat_color) ON CONFLICT (hat_name COLLATE "C" bpchar_pattern_ops) WHERE hat_color = 'green' - DO NOTHING RETURNING *; + DO NOTHING + RETURNING *; +SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename; -- Works (projects row) INSERT INTO hats VALUES ('h7', 'black') RETURNING *; @@ -1079,6 +1081,22 @@ SELECT tablename, rulename, definition FROM pg_rules WHERE tablename = 'hats'; DROP RULE hat_nosert ON hats; +-- DO NOTHING without ON CONFLICT +CREATE RULE hat_nosert_all AS ON INSERT TO hats + DO INSTEAD + INSERT INTO hat_data VALUES ( + NEW.hat_name, + NEW.hat_color) + ON CONFLICT + DO NOTHING + RETURNING *; +SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename; +DROP RULE hat_nosert_all ON hats; + +-- Works (does nothing) +INSERT INTO hats VALUES ('h7', 'black') RETURNING *; + +-- DO UPDATE with a WHERE clause CREATE RULE hat_upsert AS ON INSERT TO hats DO INSTEAD INSERT INTO hat_data VALUES ( @@ -1089,6 +1107,7 @@ CREATE RULE hat_upsert AS ON INSERT TO hats SET hat_name = hat_data.hat_name, hat_color = excluded.hat_color WHERE excluded.hat_color <> 'forbidden' RETURNING *; +SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename; -- Works (does upsert) INSERT INTO hats VALUES ('h8', 'black') RETURNING *; |