From: Fernando Hevia <fhevia@ip-tel.com.ar>
To: 'Franklin Haut' <franklin.haut@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Update PK Violation
Date: Wed, 16 Jan 2008 11:32:00 -0200
Message-ID: <001201c85844$2c8bf7d0$8f01010a@iptel.com.ar> (raw)
In-Reply-To: <478D1FB5.2070003@gmail.com>
> Franklin Haut wrote:
>
> Hi all,
>
> i have a problem with one update sentence sql.
>
> example to produce:
>
> create table temp (num integer primary key, name varchar(20));
>
> insert into temp values (1, 'THE');
> insert into temp values (2, 'BOOK');
> insert into temp values (3, 'IS');
> insert into temp values (4, 'ON');
> insert into temp values (5, 'THE');
> insert into temp values (6, 'RED');
> insert into temp values (7, 'TABLE');
>
Couldn't figure out how to do it in one sentence, still it can be done with
a function:
CREATE OR REPLACE FUNCTION insert_value(p_num integer, p_name varchar(20))
RETURNS VOID AS
$$
declare
v_num integer;
BEGIN
FOR v_num in SELECT num FROM temp WHERE num >= p_num ORDER BY num DESC
LOOP
UPDATE temp SET num = num + 1 WHERE num = v_num;
END LOOP;
INSERT INTO temp VALUES (p_num, p_name);
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
To run it:
sistema=# select insert_value(4, 'NOT');
insert_value
--------------
(1 row)
sistema=# select * from temp order by num;
num | name
-----+-------
1 | THE
2 | BOOK
3 | IS
4 | NOT
5 | ON
6 | THE
7 | RED
8 | TABLE
(8 rows)
Regards,
Fernando.
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fhevia@ip-tel.com.ar, franklin.haut@gmail.com
Subject: Re: Update PK Violation
In-Reply-To: <001201c85844$2c8bf7d0$8f01010a@iptel.com.ar>
* 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