pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jorge Maturana Ortiz <ateo@labsd.inf.utfsm.cl>
To: pgsql-sql@postgresql.org
Subject: correlative insertion
Date: Tue, 13 Oct 1998 19:18:30 -0300
Message-ID: <3623D1B6.A3ECF2AA@labsd.inf.utfsm.cl> (raw)

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

view thread (2+ messages)  latest in thread

Message-ID: <3623D1B6.A3ECF2AA@labsd.inf.utfsm.cl>
Permalink:  ../3623D1B6.A3ECF2AA@labsd.inf.utfsm.cl/
Also on:    postgresql.org/message-id/3623D1B6.A3ECF2AA@labsd.inf.utfsm.cl

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: ateo@labsd.inf.utfsm.cl
  Subject: Re: correlative insertion
  In-Reply-To: <3623D1B6.A3ECF2AA@labsd.inf.utfsm.cl>
  References: <3623D1B6.A3ECF2AA@labsd.inf.utfsm.cl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox