триггерные функции в postgresql

Ну... Вот пара ссылок из гугла для начала.

http://ciu.nstu.ru/kaf/persons/1914/study/baz_dannh/trigger

http://zxmd.wordpress.com/2010/11/04/triggers-pgsql/

Ждем обратно с более конкретными вопросами ;)

самому разобраться не получается

В чем конкретно?

CREATE OR REPLACE FUNCTION date_update() RETURNS TRIGGER AS $$
BEGIN
    IF (select date from test_table where id=new.id and text=new.text) > new.date
 
	THEN (update test_table set date=NEW.date
    END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER date_update
BEFORE INSERT OR UPDATE ON test_table FOR EACH ROW EXECUTE PROCEDURE date_update ();

проверте пожалуйста, правильно ли я написал функцию и треггер?

RETURN забыл.

Если не надо вставлять новую строку надо возвращать NULL, если надо - NEW.

мне не строку, мне дату в строке надо обновить.

Не важно.

Во-первых. На твой триггер будет ругаться при вставке в таблицу, так как он ничего не возвращает.

Во-вторых. Если ты вызываешь UPDATE таблицы в триггере на UPDATE этой таблицы у тебя кончится стек вызовов.

Надо так:

CREATE TABLE test_table
(
  id integer,
  txt text,
  modified timestamp with time zone DEFAULT now()
)
WITH (
  OIDS=FALSE
);

CREATE OR REPLACE FUNCTION date_insert() RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS ( SELECT id FROM test_table WHERE id = NEW.id AND txt = NEW.txt) THEN
	UPDATE test_table SET modified = now() WHERE id = NEW.id AND txt = NEW.txt AND modified < now();
	RETURN NULL;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS on_date_insert ON test_table;
CREATE TRIGGER on_date_insert
BEFORE INSERT ON test_table FOR EACH ROW EXECUTE PROCEDURE date_insert();


CREATE OR REPLACE FUNCTION date_update() RETURNS TRIGGER AS $$
BEGIN
  NEW.modified = now();

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS on_date_update ON test_table;
CREATE TRIGGER on_date_update
BEFORE UPDATE ON test_table FOR EACH ROW EXECUTE PROCEDURE date_update();

огромное спасибо, за работу над ошибками. такой вопрос. я так понял что триггер обновляет дату на текущую. а мне необходимо вставка даты из источника, мне надо now() поменять на New.date (то есть строка должна выглядеть так? NEW.modified = NEW.date)?

Да.

Если при UPDATE надо не текущую дату, а дату из запроса, то триггер на UPDATE вообще не нужен.

Вообще, у Postgresql довольно хорошая документация и на stackoverflow куча информации.

тут еще проблема возникла. в постгресе есть функция insert into .... returning id. то есть есть таблица с тремя полями a,b, id. a + b стоит ключь unique (что бы в базу два одинаковых значения не занести). id - pkey. дак вот. заносится в базу два значения, база возвращает для этих значений порядковый номер (сиквинс). но если делать вставку insert into .... returning id и столбцы a и b имеют такие значени, то база сматериться что имеются повторяющиеся значения, а значение id вернет не существующей записи (следующей от последней). на данный момент я решаю эту проблему так. сначало селектом проверяю есть ли такие значения, если есть то возвращаю id значение имеющейся записи. а если записи нет, то она заносится инсертом и возвращается id. триггеры ведь вещь классная, может можно написать триггер на проверку имеющихся данных и в случае их отсутствия делать инсерт. но как триггером вернуть значение id ? или может у имеющейся проблемы есть другое решение?

нашел где то на форуме вот такую заметку. может кому пригодится. вроде как раз про мой случай:

К теме постгре, перед вставкой какого либо значения необходимо проверить его на существование в БД.
Пользуюсь таким шаблоном: Создаю процедуру.

CREATE OR REPLACE FUNCTION «public».«get_server» (varchar, integer) RETURNS integer AS
$body$
DECLARE serv_count integer;
newid bigint;
BEGIN
SELECT servers.id_server INTO newid FROM servers WHERE domain = $1;
IF newid IS NULL THEN
INSERT INTO servers (domain, ip) VALUES ($1, $2);
ELSE
return newid;
END IF;
SELECT lastval() INTO newid;
RETURN newid;
END;
$body$
LANGUAGE 'plpgsql'

теперь легко просто скриптом сделать так
SELECT get_server('habra.ru', 999)
И, если данных в БД нет — они вставяться, а если есть просто произойдет возврат ID записи.
Это удобно, не загромождает исполняемый код.

В функции можно обойтись без

SELECT lastval() ...

INSERT... RETURNING id_server

Ну тогда можно написать процедурку, которая будет все делать и возвращать нужные значения id. Но тогда, если кто-то попробует сделать вставку простым INSERT, а не твоей процедурой - у него будет либо облом, либо база будет в нехорошем состоянии, что недопустимо.

Можно писать вот такой изврат вместо вставки, выбросив все триггеры:

WITH exists_seq AS (
	DELETE FROM test_table WHERE id = 2 AND txt = '4' AND modified < now() RETURNING *
) 
INSERT INTO test_table (id, txt, modified)
SELECT id, txt, now() FROM exists_seq
UNION 
SELECT 2, '4', now()
RETURNING id;

Эта фишня как раз и будет возвращать id либо новый, либо существующий. Но это на мой взгляд жесть )

а какое тогда правильное решение данного вопроса? я же как вариант предложил. может кто то более правильное решение скажет. думаю не только мне это пригодится

Запутали тебя.

Есть несколько вариантов решения твоей проблемы:

1. Повесить все на триггеры. Удобно тем, что ты просто вставляешь-обновляешь-удаляешь стандартными функциями. Минус - из триггера не возвращается, что же ты модифицировал, надо смотреть отдельно. 2. Функция-обновлятор. Удобно, если ты сам ВСЕГДА будешь пользоваться только ей. Минус - если кто-то не знает об этой функции - может сделать тебе некорректную базу.

Я бы сделал триггеры для защиты данных базы + функцию-обновлятор для удобства использования, так как она вернет тебе модифицированный id.

И ни в коем случае не используй отдельно select и ПОТОМ отдельно INSERT без общей транзакции. Между вызовами данные могут уже и поменяться.

1 - сейчас всё идет в один поток. так что на данный момент это не страшно. 2 - вот и хочу разобраться, понять, как лучше сделать

может на ту функцию которую я нашел на просторах интернета триггер поставить или на инсерт, или на селект. по подобию того как сделано в предыдущем тригере. тупо инсерт а функция сама разбирается, что делать, или обновить данные или вставку сделать? тут скажем на селект сделать, если данных нет, то вставка. опять же, сейчас задумался над тем, что программа запросит данные которые ввел пользователь их не найдет и сделает вставку, в итоге вся база мусором забьется. вообщзем на селект нельзя. остается инсерт

Триггер на селект не бывает. Триггер выполняется по изменению или попытке изменения данных.

если данных нет, то вставка

Это вообще корректное поведение программы? Смысл в такой операции?

НУ делай триггеры. Будет неудобно - сделаешь в дополнение функцию.

сейчас всё идет в один поток. так что на данный момент это не страшно.

Предполагать, как будет действовать пользователь в будущем - неверный подход. Надо делать так, чтобы у него в принципе не было возможности испортить что-либо.