Re: Using sequence name depending on other column - Mailing list pgsql-general

>> I'm trying to move existing ERP database to Postgres
>>
>> My database contains table of document headers:
>>
>> CREATE TABLE document (
>> category CHAR,
>> sequence NUMERIC(7),
>> ... a lot of other columns ... ,
>> PRIMARY KEY (category, sequence) ) ;
>>
>> and document rows:
>>
>> CREATE TABLE rows  (
>> category CHAR,
>> sequence NUMERIC(7),
>> ... a lot of other columns ... ,
>> FOREIGN KEY (category, sequence) REFERENCES document ON DELETE CASCADE );
>>
>> I need to insert always on category documents in one transaction.
>
From what I see above, I don't see any need to have separate sequences for
> each category. It looks like you can just use one for the whole table.
> That will work efficiently.

I thought about this.

1. It seems that user prefer to see separate numbers for each sequence.

First invoice has number 1 , second invoice has number  2
First order has number 1, second order has number 2 etc.

It seems that this is more convenient

2. Users may have not acces to all documents. He/she may even not to know
about existence of other categories . If it sees sequence numbers leving big
random gaps for unknown reasons this can be very confusing.

3. This is also a security leak: by analyzing sequence numbers, user can get
information about the number and insertion frequency of unauthorized
documents. This is the information which should be hidden from user.

So it seems that the best way is for mass document insertion:

1. Create separate (20!) sequences for each category.
2. Use a trigger suggested by Russell Smith for each document insertion:

CREATE FUNCTION seq_trig() RETURNS "trigger"
    AS $$BEGIN
NEW.sequence = nextval(NEW.category);
RETURN NEW;
END$$
    LANGUAGE plpgsql STRICT;

3. Grab the inserted document sequence number using curval(). Update
temporary table document rows with this number.
4. Repeat p.3 for each document separately .  It seems that this cannot be
done is a SQL way, it requires the scanning of insertable document header
database one by one.

In this case sequence number acts as registration number and as part of
primary key.

The problem is that this requires calling curval() function after inserting
each document header. This doubles
the client-server traffic compared to the solution where sequence numbers
are allocated one time from
separate lookup table.

Is this solution best or should I still use separate table for storing
numbers ?

> Though it looks like your description of the rows table is odd. My guess
> is
> that the sequence for the row is not supposed to be the same one used in
> the FK reference to the document. Assuming this is the case, again you
> can use one sequence for the entire rows table.

Sorry, I don't understand this.
The fields (category, sequence) make relation between document headers and
document rows.
They are same for same document. Sequnece numbers are generated by document
header insertion trigger.
There is no primary key required in row table.

Andrus.