Creating rule for update view sql error

Question

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.

Go back to your old questions, and accept answers if helps you. Because right now from 11 Questions you asked, only one you accepted. One does not have answer(as duplicate), other 9 question have answer but you don't eithr accept nor upvote them, so Try to accept answers, if that helps you — HaveNoDisplayName
– HaveNoDisplayName, Commented May 14, 2015 at 17:44

HaveNoDisplayName · Accepted Answer · 2015-05-14 21:55:17Z

3

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

edited May 14, 2015 at 21:55

answered May 14, 2015 at 16:47

HaveNoDisplayName

8,537106 gold badges41 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Politank-Z Over a year ago

The only difference I see between your code and OP's is the formatting. What am I missing?

HaveNoDisplayName Over a year ago

see, little different.... OP uses table name with column, and thats not required as per documentation, and that OP missing

user2950602 Over a year ago

@Piyush I tried right syntax, but got the error: ERROR: column new.name does not exist

HaveNoDisplayName Over a year ago

@user2950602:- can you post your new query where you get error?

user2950602 Over a year ago

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

|

Collectives™ on Stack Overflow

Creating rule for update view sql error

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related