Re: Please help

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.