Re: Conditions in PostGres SQL

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