Re: Please help - Mailing list pgsql-general

From Ang Chin Han
Subject Re: Please help
Date
Msg-id 3FA5F059.70502@bytecraft.com.my
Whole thread Raw
In response to Please help  (CY <cyhoong@pc.jaring.my>)
List pgsql-general
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 ;)


Attachment

pgsql-general by date:

Previous
From: CY
Date:
Subject: Please help
Next
From: Shridhar Daithankar
Date:
Subject: [Fwd: [LIH]OpenOffice.org 1.1 <-> PostgreSQL connectivity]