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(at)labsd(dot)inf(dot)utfsm(dot)cl