0

I have create a custom type like this:

CREATE TYPE my_schema.sample_type
AS
(
    id int,
    categories int[], 
    body text
);

This type is used as the array parameter in my function:

CREATE OR REPLACE FUNCTION my_schema.bulk_operation(
    array_content my_schema.sample_type[]
)
RETURNS SETOF my_schema.sample_result_type
AS $$
DECLARE
-- 
BEGIN
--
--
END;
$$ LANGUAGE plpgsql;

I'm trying to run this function from the SQL editor and could not find how to create this array parameter and pass it to the function.

My latest attempt was to create a block like below and define a variable with a single instance of my_schema.sample_type and later add it to an array before running the function:

DO
 LANGUAGE plpgsql $$
DECLARE
    my_instance my_schema.sample_type;
BEGIN
    SELECT 
      1 AS id,
      '{10, 15}':: int[] AS categories,
      'some content' as body
    INTO my_instance;
END

However it is complaining about the variable declaration.

juliano.net
  • 7,982
  • 13
  • 70
  • 164
  • Actually, my question is not about the function itself. It is how to define the value that will be passed when I call `SELECT my_schema.bulk_operation( ......... )`, considering that the parameter is an array of a custom type. – juliano.net Mar 16 '23 at 17:45
  • By the way, the function as the type was a typo, I fixed that in the question – juliano.net Mar 16 '23 at 17:47

1 Answers1

1

You can create an array of rows then cast it to your type

select ARRAY[ROW(1,'{10, 15}':: int[],'constant'),
             ROW(2,'{10, 15}':: int[],'another constant')]::my_schema.sample_type[];
JGH
  • 15,928
  • 4
  • 31
  • 48