Re: Conditions in PostGres SQL
От | Robert B. Easter |
---|---|
Тема | Re: Conditions in PostGres SQL |
Дата | |
Msg-id | 00062312550000.06580@comptechnews обсуждение исходный текст |
Ответ на | Conditions in PostGres SQL (Stephan Richter <srichter@cbu.edu>) |
Список | 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
В списке pgsql-general по дате отправления: