I wanted a auto-increasement function, similar to sequence, to increase a field. I cannot use CREATE SEQUENCE because it cd_line_no will start again with a new coursedetail.
CREATE TABLE coursedetail ( cd_cf_id char(30), cd_line_no smallint default auto_increment by 1, cd_name char(40), cd_status char(2),
CONSTRAINT cd_pkey PRIMARY KEY (cd_cf_id, cd_line_no), FOREIGN KEY (cd_cf_id) REFERENCES course (cf_id) );
Don't really understand why you can't use CREATE SEQUENCE... are you saying you need a unique, unused smallint for cd_line_no, for a given cd_cf_id (noting that (cd_cf_id, cd_line_no) is the primary key)?
This is probably a bad design, but you could try
CREATE FUNCTION cd_nextval(CHAR(30)) RETURNS smallint LANGUAGE sql AS
'SELECT coalesce(max(cd_line_no), -1) + 1 FROM coursedetail WHERE cd_cf_id = $1';
Need to change some of your application code to handle it, and your coursedetail table definition a bit, but, hey, can't do all your homework for you ;)
pgp00000.pgp
Description: PGP signature
