Good evening everyone, I was trying to make a procedure work with the types I created to clean up a little the Java code that I made to performed some data persistence, but during the creation of the function in PostgreSQL, I had a doubt on how to proceed, how would this procedure call be in this case?

CREATE TYPE store AS (
    id INTEGER,
    name VARCHAR(255),
    document VARCHAR(255)
);

CREATE TYPE inventory AS (
    id INTEGER,
    product_id INTEGER,
    store_id INTEGER,
    amount INTEGER,
    price NUMERIC(15,2)
);

CREATE TYPE product AS (
    id INTEGER,
    name VARCHAR(255),
    product_inventory inventory
);

CREATE OR REPLACE FUNCTION products_test(my_store IN store, my_products IN product[])
RETURNS product[] AS $$
BEGIN
    RETURN my_products;
END;
$$ LANGUAGE plpgsql;

SELECT products_test(ARRAY[
    '(1,"tv", INVENTORIES(1, 1, 1, 20, 20.00))',
    '(2,"PC", INVENTORIES(1, 1, 1, 20, 20.00))'
]::products[]);

When I try to call this function with the select clause above, I had the error below:

ERROR:  malformed record literal: " INVENTORIES(1"
LINE 2:  '(1,"tv", INVENTORIES(1, 1, 1, 20, 20.00))',
         ^
DETAIL:  Missing left parenthesis.
SQL state: 22P02
Character: 16

Which error could be this related? And how the correct form to call these nested composite types?

CodePudding user response：

This works.

testdb=> SELECT products_test('(1,"tv","large")', ARRAY[(1,'tv', (1, 1, 1, 20, 20.00)), (2,'PC', (1, 1, 1, 20, 20.00))]::product[]);
                         products_test
---------------------------------------------------------------
 {"(1,tv,\"(1,1,1,20,20.00)\")","(2,PC,\"(1,1,1,20,20.00)\")"}
(1 row)

Things changes are

1. I avoided INVENTORIES and just used parentheses
2. products is wrong; it should be product
3. the store parameter was missing