Невозможно выбрать из временной таблицы

У меня есть 3 таблицы - T_USER, T_PRIVILEGE и T_USER_PRIVILEGES.

T_USER_PRIVILEGES — это справочная таблица, содержащая ссылки от T_USER строк до T_PRIVILEGE строк. Я хочу удалить строку из T_USER, для чего мне нужно сначала удалить ссылки из T_USER_PRIVILEGES, а также все строки, на которые есть ссылки из T_PRIVILEGE.

Я хочу создать временную таблицу, содержащую все строки, на которые есть ссылки из T_PRIVILEGE, затем удалить все ссылки из T_USER_PRIVILEGES и, наконец, удалить все строки из T_PRIVILEGE, которые хранятся во временной таблице.

Я пытаюсь создать хранимую процедуру, которая выполняет это:

CREATE FUNCTION "SP_DELETE_USER"(userid character varying) RETURNS void AS
$BODY$CREATE TEMP TABLE temp_privilege_ids
(
    privilege_id VARCHAR(100)
);    

SELECT "PRIVILEGE_ID"
INTO temp_privilege_ids
FROM 
(SELECT * FROM "T_USER_PRIVILEGES"
WHERE "USER_ID" = userid) as foo;

DELETE FROM "T_USER_PRIVILEGES"
WHERE "USER_ID" = userid;

DELETE FROM "T_PRIVILEGE"
WHERE "ID" IN
(SELECT privilege_id FROM temp_privilege_ids);$BODY$
LANGUAGE sql VOLATILE NOT LEAKPROOF;
ALTER FUNCTION public."SP_DELETE_USER"(character varying)
  OWNER TO postgres;

userid — это параметр для SP.

Когда я пытаюсь создать SP, pgAdmin говорит:

relation "temp_privilege_ids" does not exist
LINE 19: (SELECT privilege_id FROM temp_privilege_ids);$BODY$

Я искал везде объяснение, но не нашел ответа. У кого-нибудь есть идея?

Это справочная таблица:

CREATE TABLE "T_USER_PRIVILEGES" (
  "USER_ID" character varying(100) NOT NULL,
  "PRIVILEGE_ID" character varying(100) NOT NULL,
  CONSTRAINT "PK_T_USER_PRIVILEGES" PRIMARY KEY ("USER_ID", "PRIVILEGE_ID"),
  CONSTRAINT "FK_T_USER_PRIVILEGES_PRIVILEGES" FOREIGN KEY ("PRIVILEGE_ID")
      REFERENCES "T_PRIVILEGE" ("ID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK_T_USER_PRIVILEGES_USER" FOREIGN KEY ("USER_ID")
      REFERENCES "T_USER" ("ID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE INDEX "FKI_T_USER_PRIVILEGES_PRIVILEGES"
  ON "T_USER_PRIVILEGES" ("PRIVILEGE_ID" COLLATE pg_catalog."default");

postgresql temp-tables postgresql-9.4
person Elliko    schedule 20.10.2015    source источник
comment
Не связано, но: select .. into temp_privilege_ids должно быть insert into temp_privilege_ids select .... Или лучше: используйте только один create table ... as select ...   -  person a_horse_with_no_name    schedule 20.10.2015
comment
Существование таблиц проверяется во время компиляции, и когда вы создаете функцию, таблица действительно не существует. Компилятор не знает, что таблица будет существовать во время выполнения.   -  person a_horse_with_no_name    schedule 20.10.2015
comment
Плюс: временная таблица совершенно бесполезна. Это можно сделать с помощью всего двух простых операторов удаления (или: создать ограничение FK между двумя таблицами с помощью on delete cascade)   -  person a_horse_with_no_name    schedule 20.10.2015
comment
Я не могу сделать это без временного, так как у меня есть FK и я должен сначала удалить из справочной таблицы. Также означает ли это, что я не могу использовать временные таблицы в SP?   -  person Elliko    schedule 20.10.2015
comment
Что касается FK, у меня есть два FK, выходящие из справочной таблицы в две другие таблицы, но будет ли в этом случае работать DELETE CASCADE?   -  person Elliko    schedule 20.10.2015
comment
Существуют различные решения вашей проблемы. Предоставьте полный оператор CREATE FUNCTION, вызвавший ошибку. Никогда не просто запутанный фрагмент. Заголовок функции является неотъемлемой частью.   -  person Erwin Brandstetter    schedule 21.10.2015
comment
Конечно, вы можете обойтись без временной таблицы. Вам нужно сначала удалить из T_PRIVILEGE (используя подзапрос), затем удалить из t_user_privileges. Не связано, но: вам действительно следует держаться подальше от идентификаторов в кавычках. В долгосрочной перспективе они доставляют гораздо больше хлопот, чем того стоят.   -  person a_horse_with_no_name    schedule 21.10.2015
comment
@ErwinBrandstetter Я отредактировал сообщение, чтобы оно содержало всю функцию CREATE. Сначала я добавил только тот код, который написал в редакторе кода мастера, остальное генерируется автоматически Postgres.   -  person Elliko    schedule 21.10.2015
comment
Вы пишете userid is a parameter for the SP, но в обновленном определении функции нет параметра (кстати, это функция, а не хранимая процедура — в Postgres нет фактических SP).   -  person Erwin Brandstetter    schedule 21.10.2015
comment
@a_horse_with_no_name Я новичок в Postgres, поэтому я пытался соблюдать соглашение о верхнем регистре для имен таблиц, как в SQL Server, но вы, вероятно, правы, и я должен использовать только нижний регистр. Что касается запроса, не могли бы вы привести пример?   -  person Elliko    schedule 21.10.2015
comment
@ErwinBrandstetter Вы правы. Поскольку я не мог сохранить функцию, я просто воссоздал ее сейчас и забыл добавить - это не причина ошибки, которую я получаю. Я также отредактировал код, чтобы получить параметр.   -  person Elliko    schedule 21.10.2015
comment
Вам не нужно (и, вероятно, вы не можете) удалять из t_privilege. Что делать, если есть привилегии, используемые другими пользователями, а не тем, которого вы пытаетесь удалить? Все, что вам нужно сделать, это: delete from t_user_privileges where user_id = ...; delete from t_user where id = ...;, см.: sqlfiddle.com/#!15/51097/1 Также: почему вы добавляете префикс T_ к каждому имени таблицы? Вы также добавляете префикс C_ ко всем своим классам в вашем языке программирования?   -  person a_horse_with_no_name    schedule 21.10.2015

Ответы (1)


arrow_upward
0
arrow_downward

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

Вы могли реализовать то, что вы пытаетесь реализовать с помощью функции plpgsql (применив некоторые советы @a_horse_with_no_name, уже приведенные в комментариях):

CREATE OR REPLACE FUNCTION "SP_DELETE_USER"(_userid varchar) RETURNS void AS
$func$
BEGIN
   CREATE TEMP TABLE temp_privilege_ids ON COMMIT DROP AS
   SELECT "PRIVILEGE_ID"
   FROM   "T_USER_PRIVILEGES"
   WHERE  "USER_ID" = _userid;

   DELETE FROM "T_USER_PRIVILEGES"
   WHERE "USER_ID" = _userid;

   DELETE FROM "T_PRIVILEGE" t
   USING temp_privilege_ids tmp
   WHERE t."ID" = tmp."PRIVILEGE_ID";
END
$func$ LANGUAGE plpgsql;

Но это все еще излишне запутанно. Просто используйте CTE, изменяющий данные:

WITH del1 AS (
   DELETE FROM "T_USER_PRIVILEGES"
   WHERE  "USER_ID" = _userid  -- provide userid here
   RETURNING "PRIVILEGE_ID"
   )
DELETE FROM "T_PRIVILEGE" t
USING  del1
WHERE  t."ID" = del1."PRIVILEGE_ID";

Оставив в стороне мои сомнения относительно дизайна вашей базы данных и вашего соглашения об именах.

Согласно вашему комментарию, ваше ограничение FK "FK_T_USER_PRIVILEGES_PRIVILEGES" указывает в неправильном направлении: это для случая, когда несколько пользователей могут быть связаны с одной и той же привилегией (что имело бы смысл).

Если это так, отбросьте это ограничение и вместо этого создайте его на "T_PRIVILEGE"."ID":

ALTER TABLE "T_PRIVILEGE"
ADD CONSTRAINT "FK_T_PRIVILEGE_ID" FOREIGN KEY ("ID")
   REFERENCES "T_USER_PRIVILEGES"("PRIVILEGE_ID") 
   ON UPDATE CASCADE ON DELETE CASCADE;

Затем привилегии удаляются автоматически при удалении строк в "T_USER_PRIVILEGES" из-за условия CASCADE.

Все-таки странный дизайн. Обычно «привилегии» — это то, чем могут делиться несколько пользователей...

person Erwin Brandstetter    schedule 21.10.2015
comment
delete from t_privilege потерпит неудачу, если привилегия будет использоваться другими пользователями. - person a_horse_with_no_name; 21.10.2015
comment
@a_horse_with_no_name: Ага. Сомневаюсь, что установка имеет смысл. Просто демонстрирую правильную технику. Не могу исправить сломанный дизайн здесь. Я бы тоже использовал разные имена и типы данных. - person Erwin Brandstetter; 21.10.2015
comment
Отношение между пользователем и привилегиями — один ко многим, что означает, что никто другой не будет использовать эту привилегию. - person Elliko; 21.10.2015
comment
@Elliko: Если это так, то ваше ограничение FK "FK_T_USER_PRIVILEGES_PRIVILEGES" указывает в неправильном направлении: это для случая, когда несколько пользователей могут быть связаны с одной и той же привилегией (что имело бы смысл). - person Erwin Brandstetter; 21.10.2015
comment
Это имеет смысл. И тогда я мог бы просто использовать DELETE CASCADE. Спасибо. - person Elliko; 21.10.2015