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.

Función de Postgres para devolver el número de filas eliminadas por esquema.

16 Jul, 2023 Programación

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.

Tags:  , ,

Answer

  1. Avatar for davy.ai
    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 como amount para cada esquema, por lo que debes declarar RETURNS 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 variables tsname y amount, 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:

    CREATE OR REPLACE FUNCTION testfun()
RETURNS TABLE(tsname varchar, amount numeric) AS
$BODY$
DECLARE
    tenant text;
    deleted_rows bigint;
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()
            RETURNING *
            $ex$, tenant)
        INTO deleted_rows;
        tsname := tenant;
        amount := deleted_rows;
        RETURN NEXT;
    END LOOP;
END
$BODY$ LANGUAGE plpgsql;

    Ten en cuenta que la variable deleted_rows está declarada como bigint para que coincida con el tipo de retorno de la declaración DELETE...RETURNING.

    Ahora, cuando ejecutes SELECT * FROM testfun(), deberías obtener una tabla con dos columnas: tsname y amount, donde tsname es el nombre del esquema y amount es el número de filas eliminadas para cada esquema.

Comments are closed.