From ateo@labsd.inf.utfsm.cl Tue Oct 13 22:15:47 1998 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-- From herouth@oumail.openu.ac.il Wed Oct 14 09:48:23 1998 Received: from shaked.cc.openu.ac.il (shaked.cc.openu.ac.il [147.233.145.65]) by hub.org (8.8.8/8.8.8) with ESMTP id FAA03616 for ; Wed, 14 Oct 1998 05:48:23 -0400 (EDT) (envelope-from herouth@oumail.openu.ac.il) Received: from [194.90.105.28] (herouth@[194.90.105.28]) by shaked.cc.openu.ac.il (8.8.5/8.8.5) with ESMTP id LAA09882; Wed, 14 Oct 1998 11:48:00 +0200 (IST) X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: <3623D1B6.A3ECF2AA@labsd.inf.utfsm.cl> Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Date: Wed, 14 Oct 1998 11:49:23 +0200 To: Jorge Maturana Ortiz , pgsql-sql@postgreSQL.org From: Herouth Maoz Subject: Re: [SQL] correlative insertion Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id FAA03622 At 0:18 +0200 on 14/10/98, Jorge Maturana Ortiz wrote: > > 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. Doesn't work *how*? What does it do? In a cursory look, it seems as if the function just returns NULL when the character is not known. If so, why don't you define the num field in the table as NOT NULL DEFAULT 1? In that case, inserting a NULL should invoke the default. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma