On Tue, 15 Mar 2005 08:39 pm, Andrus wrote:
> >> > I have table containing different types of documents (type A, B and C).
> >> >
> >> > Each document type must have separate sequential ID starting at 1
> >> >
> >> > ID of first inserted record of type A must be set to 1
> >> > ID of first inserted record of type B must be also set to 1
> >> > ID of second record of type A must be set to 2
> >> > etc.
> >>
> > If you are happy with the fact that a sequence may leave a whole in
> > the numbers. You are probably best to no set a default value for an
> > integer, or big integer. Then run a before trigger for each row. That
> > trigger will assign a value to the column based on the value given for
> > the type.
>
> Russell,
>
> thank you.
> I'm a new to Postgres.
> Is there any sample how to write such trigger ?
>
CREATE FUNCTION seq_trig() RETURNS "trigger"
AS $$BEGIN
IF NEW.type = 'A' THEN
NEW.sequence = nextval('a');
END IF;
IF NEW.type = 'B' THEN
NEW.sequence = nextval('b');
END IF;
RETURN NEW;
END$$
LANGUAGE plpgsql STRICT;
Something like that this may work.
> Before inserting each row it should set document id from sequence
> corresponding to insertable document type.
>
> Andrus.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
