Thread: Please help

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

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

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/interactive/functions-conditional.html#AEN9753

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