pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
correlative insertion
2+ messages / 2 participants
[nested] [flat]

* correlative insertion
@ 1998-10-13 22:18  Jorge Maturana Ortiz <ateo@labsd.inf.utfsm.cl>
  0 siblings, 1 reply; 2+ messages in thread

From: Jorge Maturana Ortiz @ 1998-10-13 22:18 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [SQL] correlative insertion
@ 1998-10-14 09:49  Herouth Maoz <herouth@oumail.openu.ac.il>
  parent: Jorge Maturana Ortiz <ateo@labsd.inf.utfsm.cl>
  0 siblings, 0 replies; 2+ messages in thread

From: Herouth Maoz @ 1998-10-14 09:49 UTC (permalink / raw)
  To: Jorge Maturana Ortiz <ateo@labsd.inf.utfsm.cl>; pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~1998-10-14 09:49 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-10-13 22:18 correlative insertion Jorge Maturana Ortiz <ateo@labsd.inf.utfsm.cl>
1998-10-14 09:49 ` Herouth Maoz <herouth@oumail.openu.ac.il>

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