From: | "Robert B(dot) Easter" <reaster(at)comptechnews(dot)com> |
---|---|
To: | Stephan Richter <srichter(at)cbu(dot)edu>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Conditions in PostGres SQL |
Date: | 2000-06-23 16:53:18 |
Message-ID: | 00062312550000.06580@comptechnews |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, 22 Jun 2000, Stephan Richter wrote:
> Hello everyone,
>
> I want to do the following SQL statement:
>
> I want to check whether an entry exists. If it does not exist, add an entry
> to the table, otherwise update the existing one:
>
> Pseudo code:
>
> IF entry IN table
> UPDATE entry
> ELSE
> ADD entry
> FI
>
> Does anyone know how to do that?
-- Here is a simple example that might be close to what you want
-- Load the PGSQL procedural language
-- This could also be done with the createlang script/program.
-- See man createlang.
CREATE FUNCTION plpgsql_call_handler()
RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so'
LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
CREATE TABLE names (
name VARCHAR(20) NOT NULL,
age INTEGER NOT NULL
);
CREATE FUNCTION names_trig() RETURNS OPAQUE AS '
DECLARE
rec names%ROWTYPE;
BEGIN
IF TG_OP = ''INSERT'' THEN
SELECT * INTO rec FROM names WHERE name = NEW.name;
IF FOUND THEN
UPDATE names SET age = NEW.age WHERE name = NEW.name;
RETURN NULL;
END IF;
RETURN NEW;
END IF;
IF TG_OP = ''DELETE'' THEN
RETURN OLD;
END IF;
IF TG_OP = ''UPDATE'' THEN
RETURN NEW;
END IF;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER names_trigger
BEFORE INSERT OR UPDATE OR DELETE ON names FOR EACH ROW
EXECUTE PROCEDURE names_trig();
-- INSERT will create new records when name is not found
-- INSERTing a name that already exists will result in an UPDATE instead
-- Nothing special happens on DELETE or UPDATE
INSERT INTO names VALUES ('Bob', 17);
INSERT INTO names VALUES ('Jim', 20);
SELECT * FROM names;
INSERT INTO names VALUES ('Bob', 18);
INSERT INTO names VALUES ('Jim', 21);
SELECT * FROM names;
--
Robert B. Easter
From | Date | Subject | |
---|---|---|---|
Next Message | Vassili A Akimov | 2000-06-23 17:43:52 | Re: Need to improve performance |
Previous Message | NRonayette | 2000-06-23 16:42:05 | Re: Show tables! |