From: Kretschmer Andreas <andreas_kretschmer@despammed.com>
To: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: [despammed] update/insert data
Date: Sun, 28 Nov 2004 14:45:58 +0100
Message-ID: <20041128134558.GA5142@kaufbach.delug.de> (raw)
In-Reply-To: <20041127175540.M63399@narrowpathinc.com>
References: <20041127175540.M63399@narrowpathinc.com>
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° ;-)
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: andreas_kretschmer@despammed.com
Subject: Re: [despammed] update/insert data
In-Reply-To: <20041128134558.GA5142@kaufbach.delug.de>
* 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