On Tue, Nov 22, 2011 at 2:05 PM, Andreas Kretschmer <akretsch...@spamfence.net> wrote: > Kenneth Tilton <ktil...@mcna.net> wrote: > >> On Tue, Nov 22, 2011 at 12:53 PM, Andreas Kretschmer >> <akretsch...@spamfence.net> wrote: >> > Kenneth Tilton <ktil...@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 ...
Ah, OK, I did not understand what they meant by "partition" when I checked out row_number. I'll give that a try. First Q: does a row have a row_number at the time of insert/before? Or will I be updating the row in the insert/after? I should know soon... :) -kenneth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general