'postgresql function to process partitions separately

I have multiple loosely connected tables, all partitioned the same way by the same key:

create table owner( user_id uuid primary key, owned_thing int ) partition by hash(user_id);
    create table owner_part1 partition of owner for values with (modulus 2, remainder 0);
    create table owner_part2 partition of owner for values with (modulus 2, remainder 1);
create table reader( user_id uuid primary key, book text ) partition by hash(user_id);
    create table reader_part1 partition of reader for values with (modulus 2, remainder 0);
    create table reader_part2 partition of reader for values with (modulus 2, remainder 1);
create table responsible(user_id uuid primary key, some_data bigint ) partition by hash(user_id);
    create table responsible_part1 partition of responsible for values with (modulus 2, remainder 0);
    create table responsible_part2 partition of responsible for values with (modulus 2, remainder 1);

I want to write a function that works on all those tables, but only one partition at a time. The reason is that I want to call this function concurrently for each partition, to process them in parallel. Something like:

CREATE OR REPLACE FUNCTION process_partition(
      partition integer
) RETURNS VOID
LANGUAGE plpgsql AS
$BODY$
BEGIN
    insert into user_part<n>;
    delete from owner_part<N>;
    ....
END
$BODY$

How can I achieve this elegantly? I can access the partition tables individually, eg select * from owners_part2. However, my problem is, that I have to hardcode the table name in the function body (the <N> in my example above). I wanted to avoid having all my complex code written inside string literals and then inserting the table names, because ppl say that this query-as-string approach is less efficient. But it also "feels wrong" to pack all my logic inside strings. I also want to avoid duplicating the body for every partition, obviously.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source