Создаю триггер с помощью миграции, PostgreSQL. Делаю запрос напрямую в БД -> отрабатывает корректно: и функция, и триггер создаются. Делаю запрос через миграцию -> получаю ошибку.
Файл миграции:
public function up()
{
$this->execute('DROP TRIGGER IF EXISTS trigger_window_view_before_del ON window_view;');
$this->execute('DROP FUNCTION IF EXISTS trigger_window_view_before_del();');
$this->execute('
CREATE FUNCTION trigger_window_view_before_del() RETURNS trigger AS $trigger_window_view_before_del$
BEGIN
IF (SELECT COUNT(*) FROM object_rent WHERE window_view ? OLD.id::text) > 0 THEN
RAISE EXCEPTION \'Sorry, cannot delete this window_view, because it used in object_rent.\';
ELSIF (SELECT COUNT(*) FROM object_sale WHERE window_view ? OLD.id::text) > 0 THEN
RAISE EXCEPTION \'Sorry, cannot delete this window_view, because it used in object_sale.\';
ELSE
DELETE FROM window_view WHERE window_view.id = OLD.id;
END IF;
END;
$trigger_window_view_before_del$ LANGUAGE plpgsql;
');
$this->execute('
CREATE TRIGGER trigger_window_view_before_del
BEFORE DELETE ON window_view FOR EACH ROW
EXECUTE PROCEDURE trigger_window_view_before_del();
');
}
Текст ошибки:
> execute SQL: DROP TRIGGER IF EXISTS trigger_window_view_before_del ON window_view; ... done (time: 0.009s)
> execute SQL: DROP FUNCTION IF EXISTS trigger_window_view_before_del(); ... done (time: 0.001s)
> execute SQL:
CREATE FUNCTION trigger_window_view_before_del() RETURNS trigger AS $trigger_window_view_before_del$
BEGIN
IF (SELECT COUNT(*) FROM object_rent WHERE window_view ? OLD.id::text) > 0 THEN
RAISE EXCEPTION 'Sorry, cannot delete this window_view, because it used in object_rent.';
ELSIF (SELECT COUNT(*) FROM object_sale WHERE window_view ? OLD.id::text) > 0 THEN
RAISE EXCEPTION 'Sorry, cannot delete this window_view, because it used in object_sale.';
ELSE
DELETE FROM window_view WHERE window_view.id = OLD.id;
END IF;
END;
$trigger_window_view_before_del$ LANGUAGE plpgsql;
...Exception 'yii\db\Exception' with message 'SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "$1" at character 201
The SQL being executed was:
CREATE FUNCTION trigger_window_view_before_del() RETURNS trigger AS $trigger_window_view_before_del$
BEGIN
IF (SELECT COUNT(*) FROM object_rent WHERE window_view ? OLD.id::text) > 0 THEN
RAISE EXCEPTION 'Sorry, cannot delete this window_view, because it used in object_rent.';
ELSIF (SELECT COUNT(*) FROM object_sale WHERE window_view ? OLD.id::text) > 0 THEN
RAISE EXCEPTION 'Sorry, cannot delete this window_view, because it used in object_sale.';
ELSE
DELETE FROM window_view WHERE window_view.id = OLD.id;
END IF;
END;
$trigger_window_view_before_del$ LANGUAGE plpgsql;
'
in /src/vendor/yiisoft/yii2/db/Schema.php:636
Error Info:
Array
(
[0] => 42601
[1] => 7
[2] => ERROR: syntax error at or near "$1" at character 201
)
Айфон мало держит заряд, разбираемся с проблемой вместе с AppLab
Перевод документов на английский язык: Важность и ключевые аспекты
Вопрос следующий имеется страничка на HTML в ней есть поля для логина и пароля для подключения к БДКак передать вводимые данные в скрипт?