Kenneth Tilton <ktilton@mcna.net> wrote:
> On Tue, Nov 22, 2011 at 12:53 PM, Andreas Kretschmer
> <akretschmer@spamfence.net> wrote:
> > Kenneth Tilton <ktilton@mcna.net> wrote:
> >
> >> Bit of a trigger NOOB Q:
> >>
> >> I am trying to use a trigger function to automatically populate new
> >> rows in a table with a public ID of the form YYYY-NNN such that the
> >> 42nd row created in 2011 would get the ID "2011-042". Each row is
> >> associated via an iasid column with a row in an audit table that has a
> >> timestamp column called created. This works OK, but I am worried about
> >> two rows getting the same case_no if they come in at the same time
> >> (whatever that means):
> >>
> >> declare
> >> case_yr integer;
> >> yr_case_count bigint;
> >> begin
> >> select date_part('year', created) into case_yr
> >> from audit
> >> where audit.sid = NEW.iasid;
> >>
> >> select count(*) into yr_case_count
> >> from fwa_case, audit
> >> where fwa_case.iasid=audit.sid
> >> and date_part('year', created) = case_yr;
> >>
> >> NEW.case_no = to_char( case_yr, '9999' ) || '-' ||
> >> to_char(1+yr_case_count, 'FM000');
> >> return NEW;
> >> end;
> >
> > If i where you, i would not use such a column. What happens if you
> > insert/delete a record?
>
> We only do logical deletes. Not sure what you mean about inserts --
inserts that change the numbering...
> that is what I am working on, and they always should get the next
> highest sequence number in a year.
>
> >
> > I would use something like to_char(row_number() over (...),'FM000') to
> > count while select.
>
> Unfortunately it must be within the year, not overall.
That is not a problem - over (...) with, for instance, PARTITION BY ...
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
