CODEWITHSUNDEEP
sqlpostgresql
user2950602
user2950602

Reputation: 395

Creating rule for update view sql error

I am trying to create UpdaeTable view.

I have table product, category, product_has_category. 

CREATE TABLE "category" (
"category_id" SERIAL NOT NULL,
"name" varchar(15) NOT NULL,
"description" varchar(150) NOT NULL,
PRIMARY KEY("category_id")
);

CREATE TABLE "product" (
"product_id" SERIAL NOT NULL,
"name" varchar(20) NOT NULL,
"price" int4 NOT NULL,
"description" varchar(200),
"country_of_origin" varchar(20) NOT NULL,
PRIMARY KEY("product_id")
);


CREATE TABLE "product_has_category" (
"NMID" SERIAL NOT NULL,
"product_id" int4 NOT NULL,
"category_id" int4 NOT NULL
);

ALTER TABLE "product_has_category" 
ADD CONSTRAINT "Ref_Product_has_Category_to_Product" FOREIGN KEY ("Product_product_id")
REFERENCES "Product"("product_id")
MATCH SIMPLE
ON DELETE CASCADE
ON UPDATE CASCADE
NOT DEFERRABLE;

And here is view that selects products, its price and categories:

CREATE VIEW products_with_categories AS
SELECT product.name AS product_name, product.price, category.name AS     category
FROM product, category, product_has_category
WHERE product_has_category.product_id = product.product_id AND
product_has_category.category_id = category.category_id
ORDER BY product_name;

I want view to be updatable and created the rule:

CREATE RULE prod_cat_upd AS ON UPDATE TO products_with_categories 
DO INSTEAD 
UPDATE product 
   SET product.name=NEW.product.name 
WHERE product.name=OLD.product.name

And I got the following error:

invalid reference to FROM-clause entry for table "product" Hint: There is an entry for table "product", but it cannot be referenced from this part of the query.

I can not understand what does this error means and how to solve this problem.

Upvotes: 1

Views: 1122

Answers (1)

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

Try This syntax to CREATE RULE, Refer for more documentation

CREATE RULE prod_cat_upd AS ON UPDATE TO products_with_categories 
DO INSTEAD 
UPDATE product 
       SET name=NEW.name
WHERE name=OLD.name

I created Two SQL Fiddle with the Sample Table

1) Sample SQL FIDDLE with solution, I provide :- Working fine 

    CREATE TABLE shoelace_data (
    sl_name    text,          -- primary key
    sl_avail   integer,       -- available number of pairs
    sl_color   text,          -- shoelace color
    sl_len     real,          -- shoelace length
    sl_unit    text           -- length unit
);

CREATE TABLE unit (
    un_name    text,          -- primary key
    un_fact    real           -- factor to transform to cm
);

CREATE VIEW shoelace AS
    SELECT s.sl_name,
           s.sl_avail,
           s.sl_color,
           s.sl_len,
           s.sl_unit,
           s.sl_len * u.un_fact AS sl_len_cm
      FROM shoelace_data s, unit u
     WHERE s.sl_unit = u.un_name;

CREATE RULE shoelace_upd AS ON UPDATE TO shoelace
    DO INSTEAD
    UPDATE shoelace_data
       SET sl_name = NEW.sl_name,
           sl_avail = NEW.sl_avail,
           sl_color = NEW.sl_color,
           sl_len = NEW.sl_len,
           sl_unit = NEW.sl_unit
     WHERE sl_name = OLD.sl_name;

2) The Code you have for CREATE RULE :- Failed and have the same error what you get

NOT Working

CREATE RULE shoelace_upd AS ON UPDATE TO shoelace
    DO INSTEAD
    UPDATE shoelace_data
       SET shoelace_data.sl_name = NEW.shoelace_data.sl_name,
           shoelace_data.sl_avail = NEW.shoelace_data.sl_avail,
           shoelace_data.sl_color = NEW.shoelace_data.sl_color,
           shoelace_data.sl_len = NEW.shoelace_data.sl_len,
           shoelace_data.sl_unit = NEW.shoelace_data.sl_unit
     WHERE shoelace_data.sl_name = OLD.shoelace_data.sl_name;

EDIT:- You are getting this error:-

ERROR: column new.name does not exist

Because your view does not have 'name' as field, your view only have these three fields :- product_name, price and category so use these fields only

CREATE RULE prod_cat_upd AS ON UPDATE TO products_with_categories 
DO INSTEAD 
UPDATE product 
       SET name=NEW.product_name
WHERE name=OLD.product_name

Upvotes: 3

Related Questions