Ошибка синтаксиса dollar quoting postgresql в Yii2 Db execute()

414
10 февраля 2017, 06:18

Создаю триггер с помощью миграции, 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
)
READ ALSO
Вывод данных в html

Вывод данных в html

Допустим у нас имеется html страницы (вывод тела):

409
Как передать данные в скрипт PHP из HTML?

Как передать данные в скрипт PHP из HTML?

Вопрос следующий имеется страничка на HTML в ней есть поля для логина и пароля для подключения к БДКак передать вводимые данные в скрипт?

321
Голосование modx revo

Голосование modx revo

Подскажите, делаю голосование

339