Please help

[CY]

Hello list

I hope someone can gave me a hint / advice on this.

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.

TQ in advance.

TABLE
=====
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)
);


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Ang Chin Han]

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 ;)
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/pfBgNYbTUIgzwfARAvusAJ9nMvlpEIIqXwm57p1YhINcQqBSnw CeOgAq
JCNEeCrY3BbdMR1AbY2SVWY=
=ef8G
-----END PGP SIGNATURE-----

[Ang Chin Han]

Reposting this back to the pgsql-general list, others might have better
insights into this.

CY wrote:

Dear Ang

Hi - thanks for your reply.

I have master/detail record where the detail records each lineitem of
each master
(similar to order / order entry type of structure). The SEQUENCE is not
suitable
being that I have many master records and won't want whole lots of
SEQUENCE table
at the backend. Thus, I wanted is a small function that is similar to
sequence in Postgresql
to do an "auto-itemising" lineitem.
Oh okay, I think I've got what you wanted to do:

Given that master contains one or more details, you'd have a table
"details" like

primary key = (master_id, detail_id);
master_id | detail_id | data...
----------+-----------+---------
1 | 1 | ....
1 | 2 |
2 | 1 |

And that when you want to insert another detail with master_id = 3, you
want it to have detail_id = 1. Or when inserting a new detail with
master_id = 1, you want detail_id = 3.
Whether it is bad design - I do not really know. Your email DID make
me think a harder
from that angle.
Okay, the function I gave before would work for single users, but it
might return the same key to different concurrent users -> bad thing.
Can't really avoid the problem if you insist on detail_id to be
sequential from 1 for every master_id.

Another way would be to use a SEQUENCE on detail_id, and bear with the
fact that it doesn't start from 1... just use

SELECT * FROM detail WHERE master_id = $foo ORDER BY detail_id;

and number them as you receive them in your application.

To get the $n-th detail, use:

SELECT * FROM detail WHERE master_id = $foo ORDER BY detail_id LIMIT $n, 1;

But that'll pose performance problems when $n is large.
Hmmmm... there're problems either way. Anyone out there with better ideas?
As I am new to Postgresql and SQL, I depend examples from guidebooks and
help from peple
like you.

BTW, how do you use COALESCE - I know it is keyword but the Postgresql
manuals had no
record of it. Could you recommend where I can find good examples of
Postgresql
- something like Postgresql Cookbook - if any.

Section 6.12.2 of the 7.3 docs:
http://www.postgresql.org/docs/7.3/i...l.html#AEN9753

The better examples are in the mailing list and the archives. Read
through them, and bookmark/save the interesting ones.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/p0w+NYbTUIgzwfARAv2xAJ0eAr/QT0SGI8I58PsBFz4VovvLOgCfYzEE
qZVG59GFYtdl2Mf2uGVTCAs=
=Jr5k
-----END PGP SIGNATURE-----