Thread: Generate char surrogate key

Hello list,

Have a table with char(3) column, the column should contain values like
'001','002','003'... and so on, keys must be correlatives no
'001','005','007' should be allowed, any idea how to get it done using a
trigger?

Thanks in advance

--

Josué Maldonado

Hello Richard,

Thanks for your idea, I had something similar but I was unable to write
it in plpgsql, now I have it done.

Thanks.


Richard Huxton wrote:

> On Friday 19 March 2004 19:25, Josué Maldonado wrote:
>
>>Hello list,
>>
>>Have a table with char(3) column, the column should contain values like
>>'001','002','003'... and so on, keys must be correlatives no
>>'001','005','007' should be allowed, any idea how to get it done using a
>>trigger?
>
>
> You can lock the table, then do something like:
>   SELECT key_column FROM table_name ORDER BY key_column DESC LIMIT 1;
> Add one to the value you get and that's the next in order.
>
> Of course that won't help you if there are deletions/renumberings etc.
>

--
Sinceramente,

Josué Maldonado

On Friday 19 March 2004 19:25, Josué Maldonado wrote:
> Hello list,
>
> Have a table with char(3) column, the column should contain values like
> '001','002','003'... and so on, keys must be correlatives no
> '001','005','007' should be allowed, any idea how to get it done using a
> trigger?

You can lock the table, then do something like:
  SELECT key_column FROM table_name ORDER BY key_column DESC LIMIT 1;
Add one to the value you get and that's the next in order.

Of course that won't help you if there are deletions/renumberings etc.

--
  Richard Huxton
  Archonet Ltd