Re: Conditions in PostGres SQL - Mailing list 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