CY wrote:
> 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 ;)