Re: Using sequence name depending on other column

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(at)postgresql(dot)org)
>
>