#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2001
    Location
    Washington DC, USA
    Posts
    156
    Rep Power
    13

    first go at plpgsql - comment?


    I'm just getting my feet with with plpgsql and was wondering if someone could take a look at my code. I've been reading 3 different books at O'Reilly Safari on PostgreSQL but they presented several ways to do things, and didn't really give a recommedation as which route to go. Also, I just have a few general questions:

    Do functions always have to return a value?
    Is it possible to invoke functions without using select?
    Are there any performance concerns with calling multiple functions from within other functions, as I've done in add_bookmark()?

    Thanks! Any comments welcome


    Code:
    -- 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';
    Last edited by cliffyman; July 12th, 2003 at 11:30 PM.

IMN logo majestic logo threadwatch logo seochat tools logo