Received: from roble.labsd.inf.utfsm.cl (root@roble.labsd.inf.utfsm.cl [200.1.19.46]) by hub.org (8.8.8/8.8.8) with ESMTP id SAA06256 for ; Tue, 13 Oct 1998 18:15:47 -0400 (EDT) (envelope-from ateo@labsd.inf.utfsm.cl) Received: from labsd.inf.utfsm.cl (rauli.labsd.inf.utfsm.cl [200.1.19.100]) by roble.labsd.inf.utfsm.cl (8.8.7/8.8.7) with ESMTP id SAA03122 for ; Tue, 13 Oct 1998 18:12:10 -0300 Sender: ateo@labsd.inf.utfsm.cl Message-ID: <3623D1B6.A3ECF2AA@labsd.inf.utfsm.cl> Date: Tue, 13 Oct 1998 19:18:30 -0300 From: Jorge Maturana Ortiz Organization: LabSD UTFSM X-Mailer: Mozilla 4.05 [en] (X11; I; SunOS 5.5.1 sun4u) MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: correlative insertion Content-Type: multipart/alternative; boundary="------------6E1549EE8224B7CC85A2520E" --------------6E1549EE8224B7CC85A2520E Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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 --------------6E1549EE8224B7CC85A2520E Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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
  --------------6E1549EE8224B7CC85A2520E--