CODEWITHSUNDEEP
postgresqlplpgsqlcascade
juk
juk

Reputation: 2329

updating cascade tables with plpgsql

So here's my update function:

CREATE OR REPLACE FUNCTION api.book_update(
  in_id        BIGINT,
  in_category  VARCHAR,
  in_published DATE,
  in_author    VARCHAR,
  in_name      VARCHAR
) RETURNS VOID AS $$
DECLARE
in_category_id BIGINT;
tmp            BIGINT;
BEGIN
  SELECT COUNT(*) INTO tmp FROM schemas.book;
  IF (NOT (tmp <> 0)) THEN
    RETURN;
  END IF;
  SELECT
    category_id
  INTO
    in_category_id
  FROM
    schemas.book
  WHERE
    id = in_id;

  SELECT category_id INTO tmp FROM schemas.category WHERE name = in_category;
  IF (NOT FOUND)
  THEN
    SELECT nextval('schemas.category_category_id_seq') INTO tmp;
    UPDATE schemas.book
    SET
      category_id = tmp,
      published   = in_published,
      author      = in_author,
      name        = in_name
    WHERE
      id          = in_id;
    INSERT INTO schemas.category (
      category_id,
      name
    ) VALUES (
      tmp,
      in_category
    );
  ELSE
    SELECT category_id INTO tmp FROM schemas.category WHERE name = in_category;
    UPDATE schemas.book
    SET
      category_id = tmp,
      published   = in_published,
      author      = in_author,
      name        = in_name
    WHERE
      id          = in_id;
  END IF;
END;
$$
LANGUAGE plpgsql;

Terminal output:

pgdb=# select * from api.book_add('aaa', '10-12-13', 'Aauthor','Name');
 book_add 
----------

(1 row)

pgdb=# select * from api.book_list;
 id | category | published  | author  | name 
----+----------+------------+---------+------
  1 | aaa      | 2013-10-12 | Aauthor | Name
(1 row)

pgdb=# select * from api.book_update(1, 'bbb', '10-12-13', 'Aauthor','Name');
ERROR:  insert or update on table "category" violates foreign key constraint "category_category_id_fkey"
DETAIL:  Key (category_id)=(2) is not present in table "book".
CONTEXT:  SQL statement "INSERT INTO schemas.category (
      category_id,
      name
    ) VALUES (
      tmp,
      in_category
    )"
PL/pgSQL function "book_update" line 31 at SQL statement
pgdb=#

Why is it not present, when I'm updating category_id to tmp in "book" and afterwards inserting this tmp into "category" table
Here's full schema script Suggestions on it very appreciated, as I'm nw

Upvotes: 0

Views: 402

Answers (1)

wildplasser
wildplasser

Reputation: 44250

Normally a book should refer to a category instead of vice-versa. There is a N::1 relation of books to categories; two books could refer to the same category.

DROP SCHEMA IF EXISTS tmpschemas CASCADE;
CREATE SCHEMA tmpschemas;

CREATE TABLE tmpschemas.category
  ( category_id  BIGSERIAL PRIMARY KEY
  , catname         VARCHAR   NOT NULL
  , UNIQUE(catname)
  , CHECK (catname <> '')
);

CREATE TABLE tmpschemas.book
  ( id           BIGSERIAL PRIMARY KEY
  , category_id  BIGINT    NOT NULL REFERENCES tmpschemas.category(category_id) -- ON DELETE CASCADE ON UPDATE RESTRICT
  , published    DATE      NOT NULL
  , author       VARCHAR   NOT NULL
  , bookname         VARCHAR   NOT NULL
  , UNIQUE(category_id, published, author, bookname)
  , CHECK (TEXT(published) <> '')
  , CHECK (author <> '')
  , CHECK (bookname <> '')
);

Note I removed you cascade stuff, I'd presume that the categories are reasonably stable (who would want to update a serial column anyway) Cascade on delete would be even more tricky; I don't think you want to throw away all the books on gardeing, just because the category "gardening ceases to exists. Categories should never be deleted.

NOTE: if you want a book to be able to belong to more than one category, you should use a N::M junction table.

UPDATE: the reduced function:

CREATE OR REPLACE FUNCTION tmpapi.book_add(
  in_catname  VARCHAR,
  in_published DATE,
  in_author    VARCHAR,
  in_bookname      VARCHAR
) RETURNS VOID AS $$
BEGIN

  INSERT INTO tmpschemas.category(catname)
  SELECT in_catname
  WHERE NOT EXISTS (
        SELECT *
        FROM tmpschemas.category nx
        WHERE nx.catname = in_catname
        );

    INSERT INTO tmpschemas.book ( category_id, published, author, bookname)
    SELECT cc.category_id
        , in_published,in_author, in_bookname
    FROM tmpschemas.category cc
    WHERE cc.catname = in_catname
        ;

END;
$$
LANGUAGE plpgsql;

SELECT tmpapi.book_add('gardening', '2013-01-06' , 'Maggie Thatcher' , 'Destructing flowers Thoroughly' );
SELECT tmpapi.book_add('gardening', '1980-05-26' , 'Umberto Eco' , 'The name of the rose' );

SELECT * FROM tmpschemas.book;

RESULT:

 id | category_id | published  |     author      |            bookname            
----+-------------+------------+-----------------+--------------------------------
  1 |           1 | 2013-01-06 | Maggie Thatcher | Destructing flowers Thoroughly
  2 |           1 | 1980-05-26 | Umberto Eco     | The name of the rose
(2 rows)

As you can see, both books refer to the same category_id.

UPDATE2: here is the update function:

CREATE OR REPLACE FUNCTION tmpapi.book_update(
  in_id        BIGINT,
  in_catname  VARCHAR,
  in_published DATE,
  in_author    VARCHAR,
  in_bookname      VARCHAR
) RETURNS VOID AS $$
BEGIN

  INSERT INTO tmpschemas.category(catname)
  SELECT in_catname
  WHERE NOT EXISTS (
        SELECT *
        FROM tmpschemas.category nx
        WHERE nx.catname = in_catname
        )
        -- avoid inserting a category
        -- if the book to be updated does not exist
  AND EXISTS (
        SELECT * FROM tmpschemas.book bk
        WHERE bk.id = in_id
        )
        ;

    UPDATE tmpschemas.book bk
      SET category_id = cc.category_id
        , published = in_published
        , author = in_author
        , bookname = in_bookname
    FROM tmpschemas.category cc
    WHERE cc.catname = in_catname
      AND bk.id = in_id
        ;

END;
$$
LANGUAGE plpgsql;

SELECT tmpapi.book_update(1, 'politics', '2013-01-06' , 'Maggie Thatcher' , 'Destructing flowers Thoroughly' );
SELECT * FROM tmpschemas.book;

And the results:

CREATE FUNCTION
 book_update 
-------------

(1 row)

 id | category_id | published  |     author      |            bookname            
----+-------------+------------+-----------------+--------------------------------
  2 |           1 | 1980-05-26 | Umberto Eco     | The name of the rose
  1 |           2 | 2013-01-06 | Maggie Thatcher | Destructing flowers Thoroughly
(2 rows)

CREATE VIEW
 id | category  | published  |     author      |            bookname            
----+-----------+------------+-----------------+--------------------------------
  2 | gardening | 1980-05-26 | Umberto Eco     | The name of the rose
  1 | politics  | 2013-01-06 | Maggie Thatcher | Destructing flowers Thoroughly
(2 rows)

Upvotes: 2

Related Questions