0

I want to write a function that accepts array of arrays of my composite type. because of they have different lengths, it can NOT be defined as one array. So, I want to use variadic to accept arrays separately from each other. What can I do to achieve this ?

my type:

create type column_data as
(
    "name"         text,
    "type"         text
);

A function that accepts multiple arrays of my type:

create or replace function delete_from_table(table_name regclass, data jsonb, variadic columns column_data[][]) returns jsonb
    language plpgsql
as
$BODY$
.
.
.
.
.
$BODY$;

call:

select delete_from_table('cm.clients_rate_limits'::regclass,
                         '[]'::jsonb,
                         array [('pk', 'bigint')]::column_data[],
                         array [('id', 'uuid')]::column_data[],
                         array [('client_id', 'bigint'), ('rate_limit_id', 'bigint')]::column_data[]
       );

error:

[42704] ERROR: could not find array type for data type column_data[]

3
  • That won't work, because column_data[] is the same as column_data[][] in PostgreSQL. Perhaps there is some other way to achieve what you want, but I don't understand what you want. What is the function supposed to do, and what is the meaning of the parameters? Commented Sep 8, 2024 at 13:15
  • thanks @LaurenzAlbe for your replay. this function is going to generate and execute dynamic SQL to delete from tables. each element of array columns_data[][] is a unique index (one or many columns) that will be in where clause of delete query. the second argument, data jsonb contains array of json object which contains one of these keys and values ( pk, id or both (client_id and rate_limit_id) ). final query is here : db-fiddle.com/f/ffF5FLZ4Lz7aDjYkQQQRq9/… Commented Sep 8, 2024 at 13:25
  • If you don't get a good answer here, you could try on the pg-general mailing list? Commented Sep 9, 2024 at 10:33

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.