Использование CTE в plpgsql

Я пытаюсь создать функцию plpgsql в Postgres 8.4, которая использует CTE внутри, но это дает мне синтаксическую ошибку. Они не разрешены?

(Что-то вроде этого, имейте в виду, что я пишу это без своего кода.)

With foo as (SELECT id,a as alias FROM foo);
UPDATE zoo SET b = alias FROM foo WHERE id = foo.id;
^Error here

postgresql common-table-expression plpgsql
person jpp1jpp1    schedule 28.05.2012    source источник
comment
убери точку с запятой после foo)   -  person wildplasser    schedule 28.05.2012
comment
Это не проблема, я уже пробовал.   -  person jpp1jpp1    schedule 30.05.2012

Ответы (2)


arrow_upward
3
arrow_downward 

WITH foo as (
    SELECT id
       , a AS zalias
    FROM footable
    )
UPDATE zoo z
SET b = f.zalias
FROM foo f
WHERE z.id = f.id
   ;

ПРИМЕЧАНИЕ. «псевдоним» является зарезервированным словом.

ОБНОВЛЕНИЕ согласно комментарию Эрвина Брандштеттера: CTE недействителен в операторе ОБНОВЛЕНИЯ в 8.4. Вам понадобится 9.1 или выше. Поскольку CTE на самом деле является своего рода мгновенным представлением, вы можете поместить тело CTE внутрь представления и ссылаться на него.

CREATE VIEW foo as (
    SELECT id
       , a AS zalias
    FROM footable
    );
UPDATE zoo z
SET b = f.zalias
FROM foo f
WHERE z.id = f.id
   ;
DROP VIEW foo;
person wildplasser    schedule 28.05.2012
comment
alias является зарезервированным словом только в SQL:1999. , но не в более поздних версиях стандарта и не в PostgreSQL. Так что это должно быть безопасно для свободного использования. - person Erwin Brandstetter; 28.05.2012
comment
Я исправляюсь. Кажется, я помню, что когда у функций не было именованных параметров (стр. 8.3?), псевдоним был ключевым словом. Я все еще предпочитаю избегать этого. - person wildplasser; 28.05.2012
comment
Спасибо, в итоге я использовал подход временного представления. - person jpp1jpp1; 30.05.2012

arrow_upward
2
arrow_downward

данные, изменяющие CTE (вот что есть) недоступно в PostgreSQL 8.4.

Ваше утверждение будет работать с небольшими исправлениями, как демонстрирует @wildplasser в PostgreSQL 9.1 или более поздней версии, где были введены данные, изменяющие CTE.

Очень простой заменой 8.4 может быть подзапрос:

UPDATE zoo z
SET    b = f.alias
FROM  (SELECT id, a as alias FROM foo) f
WHERE  z.id = f.id;

Пример можно еще больше упростить (но, возможно, реальный случай более сложен):

UPDATE zoo z
SET    b = f.a
FROM   foo f
WHERE  z.id = f.id;

Не забудьте квалифицировать в таблице неоднозначное имя столбца id в предложении WHERE.

person Erwin Brandstetter    schedule 28.05.2012
comment
Действительно ли это CTE, изменяющий данные? Похоже на обычный, так как это не оператор DDL внутри CTE. - person vyegorov; 28.05.2012
comment
@vyegorov: Я сам сначала не был уверен. Но я изучил руководство и попробовал тестовый пример на кластере 8.4: никакого удовольствия. - person Erwin Brandstetter; 28.05.2012
comment
Я не решался добавить это окончательное сокращение к update ... FROM ... - person wildplasser; 29.05.2012
comment
@wildplasser: я добавил это только после того, как увидел, что вы этого не сделали ... :) - person Erwin Brandstetter; 29.05.2012
comment
РЖУ НЕ МОГУ. В большинстве случаев CTE будет использоваться для переноса более сложных запросов. Включая вложенные CTE. Может, мне тоже добавить рекурсивную версию? ;-) Кстати: моя привычка добавлять 'z' к подозрительным ключевым словам должна произноситься так же, как немцы произносят 'the' ;-) ТАКЖЕ: ни одно ключевое слово SQL не начинается с 'z'. - person wildplasser; 29.05.2012