July 12th, 2003, 11:25 PM
Join Date: May 2001
Location: Washington DC, USA
Time spent in forums: 26 m 7 sec
Reputation 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
-- 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 '
SELECT INTO max_rank
MAX(rank) FROM bookmark
WHERE parent_id = $1;
IF max_rank IS NULL THEN
max_rank = 0;
' 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 '
max_rank := get_child_max_rank($1);
INSERT INTO bookmark
(parent_id, description, url, rank)
VALUES ($1, $2, $3, max_rank + 1);
' LANGUAGE 'plpgsql';
Last edited by cliffyman : July 12th, 2003 at 11:30 PM.