Función de Postgres para devolver el número de filas eliminadas por esquema.
Estoy tratando de adaptar un procedimiento almacenado de Postgres a una función para proporcionar comentarios al llamante.
El procedimiento borra filas condicionalmente en esquemas específicos, y quiero que la función haga lo mismo, pero también devuelva la cantidad de filas que se borraron para cada esquema.
El procedimiento almacenado original es:
create or replace procedure clear_tenants()
language plpgsql as $function$
declare
tenant text;
begin
for tenant in
select tenant_schema
from public.tenant_schema_mappings
loop
execute format($ex$
delete from %I.parent
where expiration_date_time < now()
$ex$, tenant);
end loop;
end
$function$;
Mi transformación actual a una función es:
CREATE OR REPLACE FUNCTION testfun()
RETURNS TABLE(tsname varchar, amount numeric) AS
$BODY$
declare
tenant text;
trow record;
BEGIN
for tenant in
select tenant_schema
from public.tenant_schema_mappings
loop
execute format($ex$
WITH deleted AS (
delete from %I.parent
where expiration_date_time < now()
IS TRUE RETURNING *
)
tsname := tenant;
amount := (SELECT * FROM deleted;);
return next;
$ex$, tenant);
end loop;
END
$BODY$ language plpgsql;
Esto probablemente esté mal de muchas maneras. Definitivamente estoy confundido.
Cuando ejecuto esto con SELECT * FROM testfun()
, obtengo el siguiente error:
ERROR: error de sintaxis en o cerca de "tsname"
LÍNEA 7: tsname := tenant;
^
CONSULTA:<br>
WITH deleted AS (
delete from anhbawys.parent
where expiration_date_time < now()
IS TRUE RETURNING *
)
tsname := tenant;
amount := (SELECT * FROM deleted;);
return next;
Por lo tanto, claramente no estoy asignando correctamente las columnas de la fila, pero no estoy seguro de cómo hacerlo.
He encontrado esta pregunta (https://dba.stackexchange.com/a/219626/241662) que parecía similar, pero es un poco compleja para mi comprensión.
davy.ai
En tu intento de transformar el procedimiento almacenado en una función, parece que has mezclado un poco la sintaxis de las declaraciones SQL y el código PL/pgSQL. Aquí están los cambios que recomendaría:
Primero, necesitas declarar el tipo de retorno de tu función. En este caso, quieres devolver tanto
tsname
comoamount
para cada esquema, por lo que debes declararRETURNS TABLE(tsname varchar, amount numeric)
.A continuación, dentro del bucle, puedes usar una declaración
DELETE...RETURNING
para eliminar las filas y devolver el número de filas eliminadas. Luego puedes asignar el inquilino y el número de filas eliminadas a las variablestsname
yamount
, respectivamente.Finalmente, puedes usar la declaración
RETURN NEXT
para devolver una fila para cada esquema. Así es como debería verse el código de la función actualizado:Ten en cuenta que la variable
deleted_rows
está declarada comobigint
para que coincida con el tipo de retorno de la declaraciónDELETE...RETURNING
.Ahora, cuando ejecutes
SELECT * FROM testfun()
, deberías obtener una tabla con dos columnas:tsname
yamount
, dondetsname
es el nombre del esquema yamount
es el número de filas eliminadas para cada esquema.