INSERT into DB2 with a user defined type column

Question

Hi i am trying to explore DB2 with user defined types I have created a type and a table but i am unable to insert the data into table now. My user defined type in DB is as follows:

CREATE TYPE pizza AS
(type varchar(50),
size INTEGER,
topping varchar(50))
MODE DB2SQL@

Then i have created a table as follows:

CREATE TABLE orders
(id  INT NOT NULL PRIMARY KEY,
customer VARCHAR(50),
pizza_row pizza)@

Then when i try to insert i got error here is my insert statement:

INSERT into orders (id,customer,pizza_row) 
VALUES (1,'Larry', pizza('margarita',10,'kebab'))@

Error received:

No authorized routine named "PIZZA" of type "FUNCTION" having compatible arguments was found.. SQLCODE=-440

What is the correct syntax of inserting into my schema?

Answer 1

The default constructor function, which DB2 generates automatically when a structured type is created, has no parameters: pizza(). If you want to instantiate your type with some values, you'll need to create your own constructor function that would call the default constructor, then assign appropriate values to the type's fields, something along these lines:

create or replace function pizza (
 p_type varchar(50),
 p_size INTEGER,
 p_topping varchar(50)
)
returns pizza
begin atomic
  declare t pizza;
  set t = pizza();
  set t..type = p_type;
  set t..size = p_size;
  set t..topping = p_topping;
  return t;
end

PS. Code not tested.