Postgres function to return number of rows deleted per schema

December 9, 2021

I’m trying to adapt a Postgres stored procedure into a function in order to provide some feedback to the caller.

The procedure conditionally deletes rows in specific schemas, and I’d want the function to do the same, but also return the amount of rows that were deleted for each schema.

The original stored procedure is:

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$;

My current transform into a function is:

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;

This is probably wrong in all kinds of ways. I’m definitely confused.

When executing this with SELECT * FROM testfun(), I get the following error:

ERROR:  syntax error at or near "tsname"
LINE 7:    tsname := tenant;
           ^
QUERY:  
            WITH deleted AS (
                delete from anhbawys.parent 
                where expiration_date_time < now()
                IS TRUE RETURNING *
            )
            tsname := tenant;
            amount := (SELECT * FROM deleted;);
            return next;
            
CONTEXT:  PL/pgSQL function testfun() line 9 at EXECUTE
SQL state: 42601

So clearly I’m not properly assigning the row’s columns, but I’m not sure how.

I have found this question which seemed similar, but it’s bit complex for my understanding.

>Solution :

You can use GET DIAGNOSTICS after a DELETE statement to get the number of rows deleted:

CREATE OR REPLACE FUNCTION testfun()
   RETURNS TABLE(tsname varchar, amount bigint) AS
$BODY$
declare
    tenant text;
BEGIN
    for tenant in
        select tenant_schema 
        from tenant_schema_mappings
    loop
        execute format(
           'delete from %I.parent 
            where expiration_date_time < now()', tenant);

        tsname := tenant;
        GET DIAGNOSTICS amount := ROW_COUNT;

        return next;
    end loop;
END
$BODY$ 
language plpgsql;