Skip site navigation (1) Skip section navigation (2)

Re: Update PK Violation

From: "Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)ar>
To: "'Franklin Haut'" <franklin(dot)haut(at)gmail(dot)com>,<pgsql-sql(at)postgresql(dot)org>
Subject: Re: Update PK Violation
Date: 2008-01-16 13:32:00
Message-ID: 001201c85844$2c8bf7d0$8f01010a@iptel.com.ar (view raw or flat)
Thread:
Lists: pgsql-sql
> 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.


In response to

pgsql-sql by date

Next:From: Achilleas MantziosDate: 2008-01-16 14:30:14
Subject: Re: Update PK Violation
Previous:From: Richard HuxtonDate: 2008-01-16 09:21:37
Subject: Re: obtaining the query string inside a trigger

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group