correlative insertion

Hi,

I have a table 'mas' with the following composition:

Table    = mas
+----------------------------------+----------------------------------+-------+

|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+

| cha                              | char
|     1 |
| num                              | int4
|     4 |
+----------------------------------+----------------------------------+-------+

and this data:

  cha|num
---+---
    a  |  1
    a  |  2

I want to create a function that automatically increments the number
stored in 'num' when i insert a new tuple if it contain a 'known' cha,
for example, if i insert another 'a' in the field 'cha',  the num must
be 3.

I try:

create function otro(char) returns int4 as 'select 1 + max(num) from mas
where cha = $1;' language 'sql';

it works OK if i make the following:

insert into mas values ('a',otro('a'));

i obtain:

  cha|num
---+---
    a  |  1
    a  |  2
    a  |  3


THE PROBLEM is that when i try to insert a tuple with an unknow cha,
this function don't work. I want make an insert like:

insert into mas values ('b',otro('b'));

and obtain:

  cha|num
---+---
    a  |  1
    a  |  2
    a  |  3
    b  |  1

I would thank any help.


--
Jorge Maturana Ortiz
Laboratorio de Sistemas Distribuidos - Departamento de Informatica
Universidad Tecnica Federico Santa Maria, Valparaiso - Chile
mailto:ateo@labsd.inf.utfsm.cl