Postgres function to return number of rows deleted per schema

Question

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.

Related: https://stackoverflow.com/a/8449341/939860 – Erwin Brandstetter Dec 09 '21 at 21:06 — Erwin Brandstetter, Dec 09 '21 at 21:06

score 1 · Accepted Answer · answered Dec 09 '21 at 20:51

1

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;

answered Dec 09 '21 at 20:51

If a simultaneous request from another transaction issued a `DELETE` on the same table, would that be reflected in those stats, or they are properly isolated to only the current transaction? – payne Dec 09 '21 at 20:56
Those aren't "statistics". It's the actual number of deleted rows from the last statement. It's the same information that `psql` shows when you delete rows. It is specific to the last statement and only that. It wouldn't be really useful if that couldn't be run concurrently. – Dec 09 '21 at 20:58

VynlJunkie · Answer 2 · 2021-12-09T20:59:27.953

0

If the answer is as simple as your question there is a system catalog for this.

select schemaname , count(n_tup_del)
from pg_catalog.pg_stat_all_tables psat
group by schemaname

You can use get diagnostics (noddy function to get my point across)

create or replace function delfunc()
returns void 
as $$
declare 
 affected_rows int ;
begin 
    delete from atable where a > 998 ;
   GET DIAGNOSTICS affected_rows = ROW_COUNT;
   insert into logtable values(affected_rows);
end $$
language plpgsql

edited Dec 09 '21 at 20:59

answered Dec 09 '21 at 20:36

VynlJunkie

1,953
22
26

Interesting. Although I'm not quite sure: **1)** how to plug this in properly **2)** if it'll always only give stats related to my function (I wouldn't want other simultaneous `DELETE` operations to be reflected in here) – payne Dec 09 '21 at 20:41
Ah I understand what you mean. No, my question is a summary, but it relates specifically to "amount of rows deleted by the `EXECUTE` statement within the function". – payne Dec 09 '21 at 20:45
@payne updated with a more suitable suggestion – VynlJunkie Dec 09 '21 at 21:00

Postgres function to return number of rows deleted per schema

2 Answers2

Linked