first go at plpgsql - comment?

-- create table to hold bookmarks
CREATE TABLE bookmark
(
  id serial NOT NULL
, parent_id int4 NOT NULL
, description varchar(64) NOT NULL
, url varchar(255)
, rank int4 NOT NULL DEFAULT 1
, date_created timestamp(0) with time zone DEFAULT CURRENT_TIMESTAMP
, date_updated timestamp(0) with time zone DEFAULT CURRENT_TIMESTAMP
, PRIMARY KEY (id)
);

INSERT INTO bookmark (id, parent_id, description, rank) VALUES (0,0,'Bookmarks',0);

-- implement parent/child integrity
ALTER TABLE bookmark
ADD FOREIGN KEY (parent_id) 
REFERENCES bookmark (id)
ON UPDATE CASCADE
ON DELETE CASCADE;

-- implement rank unique index
CREATE UNIQUE INDEX bookmark_u1
ON bookmark (parent_id, rank);

INSERT INTO bookmark (parent_id, description, rank) VALUES (0,'News',1);
INSERT INTO bookmark (parent_id, description, rank) VALUES (0,'Programming',2);
INSERT INTO bookmark (parent_id, description, rank) VALUES (0,'Entertainment',3);
INSERT INTO bookmark (parent_id, description, rank) VALUES (0,'Financial',4);

-- create function to get max rank of all children from input parent_id
CREATE OR REPLACE FUNCTION get_child_max_rank(bookmark.parent_id%TYPE) RETURNS INTEGER AS '

DECLARE
  
  max_rank bookmark.rank%TYPE;
   
BEGIN
  
  SELECT INTO max_rank
    MAX(rank) FROM bookmark
    WHERE parent_id = $1;

  IF max_rank IS NULL THEN
    max_rank = 0;
  END IF;  

  return max_rank;

END;

' LANGUAGE 'plpgsql';



-- create function to add bookmark
CREATE OR REPLACE FUNCTION add_bookmark(bookmark.parent_id%TYPE, bookmark.description%TYPE, bookmark.url%TYPE) RETURNS BOOLEAN AS '

DECLARE
  
  max_rank bookmark.rank%TYPE;
     
BEGIN
  
  max_rank := get_child_max_rank($1);

  INSERT INTO bookmark
  (parent_id, description, url, rank)
  VALUES ($1, $2, $3, max_rank + 1);

  RETURN true;

END;

' LANGUAGE 'plpgsql';