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.