Date: Sun, 28 Nov 2004 14:45:58 +0100
From: Kretschmer Andreas <andreas_kretschmer@despammed.com>
To: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: [despammed] update/insert data
Message-ID: <20041128134558.GA5142@kaufbach.delug.de>
References: <20041127175540.M63399@narrowpathinc.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20041127175540.M63399@narrowpathinc.com>
User-Agent: Mutt/1.3.28i

am  Sat, dem 27.11.2004, um 12:55:40 -0500 mailte Keith Worthington folgendes:
> Hi All,
> 
> I have two tables in different schemas.  The first table in the data_transfer
> schema is loaded with a COPY command.  I need to transfer the data to the
> second schema inserting new records and updating existing records.  What is
> the best way to achieve this functionality?

You can write a trigger-function. There you can do a insert/update for
everey new records in the data_transfer - table. 

A simple example:

I hava 2 tables:

,----
| test_db=# \d namen;
|                                 Tabelle »public.namen«
|   Spalte  |        Typ        |                       Attribute
| ----------+-------------------+-------------------------------------------------------
|  id       | integer           | not null default nextval('public.namen_id_seq'::text)
|  vorname  | character varying |
|  nachname | character varying |
| Trigger:
|     trig1 BEFORE INSERT OR UPDATE ON namen FOR EACH ROW EXECUTE PROCEDURE trigg1()
|
| test_db=# \d namen2;
|          Tabelle »public.namen2«
|   Spalte  |        Typ        | Attribute
| ----------+-------------------+-----------
|  id       | integer           |
|  vorname  | character varying |
|  nachname | character varying |
|
`----

And this trigger-function:

,----
| create or replace function trigg1() returns trigger as'
| begin
| insert into namen2 values (NEW.id, NEW.vorname, NEW.nachname);
| return NEW;
| end;
| ' language plpgsql;
`----

There isn't a check for update, but this is also possible.


,----
| test_db=# select * from namen;
|  id | vorname | nachname
| ----+---------+----------
| (0 Zeilen)
|
| test_db=# select * from namen2;
|  id | vorname | nachname
| ----+---------+----------
| (0 Zeilen)
`----


And i have a file:

,----
| kretschmer@kaufbach:~$ cat input.txt
| copy "namen" from stdin;
| 10      Magdalena       Kretschmer
| 11      Katharina       Kretschmer
| kretschmer@kaufbach:~$
`----


,----
| test_db=# \i input.txt
| test_db=# select * from namen;
|  id |  vorname  |  nachname
| ----+-----------+------------
|  10 | Magdalena | Kretschmer
|  11 | Katharina | Kretschmer
| (2 Zeilen)
|
| test_db=# select * from namen2;
|  id |  vorname  |  nachname
| ----+-----------+------------
|  10 | Magdalena | Kretschmer
|  11 | Katharina | Kretschmer
| (2 Zeilen)
`----


sorry about my bad english.
-- 
Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau-
fenden Pinguins aus artgerechter Freilandhaltung.   Er ist garantiert frei
von Micro$oft'schen Viren. (#97922 http://counter.li.org)     GPG 7F4584DA
Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)