es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

  • es.davy.ai/
  • Programación /
  • Postgres: vista actualizable (con cláusula “where in”) con opción de verificación.

Postgres: vista actualizable (con cláusula “where in”) con opción de verificación.

21 Jul, 2023 Programación

Intento crear vistas actualizables con la opción “check option”. Cuando tengo uniones en los criterios de filtrado, utilizo una cláusula “where in” para simplificar las consultas. Sin embargo, si declaro la vista con ambas opciones “check option” y “where in”, la validación de datos no parece confiable. No veo esta restricción en el documento: https://www.postgresql.org/docs/current/sql-createview.html

¿Es un error? ¿Hay alguna solución alternativa?

create table tableA (
    db_record_id serial NOT null PRIMARY KEY,
    "name" varchar(60) NOT null UNIQUE
);

create or replace view viewSimpleA
as select * from tableA where name like 'a%'
with check option;

create or replace view viewA
as select * from tableA
where db_record_id in (
    select db_record_id from tableA
    where name like 'a%'
) with check option;

insert into viewSimpleA(name) values('abc'); -- OK
delete from tableA;

insert into viewA(name) values('abc'); -- KO, Error de SQL [44000]: ERROR: nueva fila viola la opción de verificación de la vista "viewa"
delete from tableA;

insert into viewSimpleA(name) values('abc');
update viewSimpleA set name = 'fine'; -- OK: la actualización es evitada por la opción de verificación
delete from tableA;

insert into viewA(name) values('abc');
update viewA set name = 'fine'; -- KO: la actualización se ejecuta, la vista está ahora vacía
delete from tableA;

select version(); -- PostgreSQL 13.5 (Debian 13.5-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

EDITAR: He creado un informe de error aquí: enlace al informe de error

Tags:  , ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Después de probar tu código y leer la documentación, parece que este comportamiento no es un error, sino más bien una limitación de la opción check. La cláusula where in en tu consulta viewA está causando que la opción check falle al insertar nuevas filas que no cumplen con los criterios de filtro. La opción check está diseñada para evitar que se inserten, actualicen o eliminen filas que no coincidan con los criterios de filtro en la vista.

    Una solución alternativa para esta limitación es crear un disparador en la tabla base tableA que aplique la misma condición de filtrado que la opción check en tu consulta viewA. De esta manera, cualquier inserción, actualización o eliminación en la tabla base también se evitará si no cumple la condición de filtro.

    A continuación se muestra un ejemplo de un disparador que aplica esta condición de filtro:

    create or replace function enforce_filter_condition()
returns trigger language plpgsql
as $$
begin
  if not exists (
    select 1 from tableA
    where db_record_id = new.db_record_id and name like 'a%'
  ) then
    raise exception 'La nueva fila viola la condición de filtro';
  end if;
  return new;
end;
$$;

create trigger check_filter_condition
before insert or update or delete on tableA
for each row
execute function enforce_filter_condition();

    Este disparador verificará si una nueva fila que se inserte, actualice o elimine de tableA cumple la misma condición de filtro que tu consulta viewA. Si no la cumple, se generará una excepción y se abortará la operación.

    Otra solución alternativa es evitar el uso de la opción check con una cláusula where in, y en su lugar usar una subconsulta en la cláusula select de tu vista. Esto hará que la vista devuelva solo filas que cumplan con los criterios de filtro, pero no evitará las inserciones, actualizaciones o eliminaciones en la vista o en la tabla base.

    create or replace view viewA
as select *
from (
  select *
  from tableA
  where name like 'a%'
) filtered_table
with check option;

    Ten en cuenta que este enfoque no proporcionará el mismo nivel de protección que la opción check, ya que las actualizaciones, inserciones o eliminaciones en la tabla base o en la vista directamente no se filtrarán.

Comments are closed.