I have the following problem with two procedures updating a product and its attributes on Postgresql 9.4. First off the tables:
CREATE TABLE product
(
id uuid NOT NULL,
version bigint NOT NULL,
is_part_of_import_id uuid,
name text NOT NULL,
owner_id uuid NOT NULL,
replaced_by_id uuid,
state character varying(255) NOT NULL,
replaces_id uuid,
last_update timestamp without time zone,
...
)
CREATE TABLE attribute_value
(
id uuid NOT NULL,
version bigint NOT NULL,
last_update timestamp without time zone,
product_id uuid NOT NULL,
replaced_by_id uuid,
replaces_id uuid,
value text,
...
)
There is a one-to-many relationship between product and attribute_value. I have a procedure to update the attribute_value
CREATE OR REPLACE FUNCTION update_attribute_value(attr_val attribute_value)
RETURNS UUID AS
$BODY$
DECLARE new_id UUID;
BEGIN
attr_val.replaces_id := attr_val.id;
attr_val.id := uuid_generate_v4();
attr_val.last_update := NOW();
INSERT INTO attribute_value SELECT attr_val.* RETURNING attribute_value.id INTO new_id;
UPDATE attribute_value SET replaced_by_id = new_id WHERE id = attr_val.replaces_id;
RETURN attr_val.id;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
and a procedure to update the product calling the update_attribute_value_procedure to update all attribute_values of the product as well
CREATE OR REPLACE FUNCTION update_product(prod product)
RETURNS UUID AS
$BODY$
DECLARE new_id UUID;
DECLARE attr_val attribute_value%ROWTYPE;
BEGIN
prod.replaces_id := prod.id;
prod.id := uuid_generate_v4();
prod.last_update := NOW();
INSERT INTO product SELECT prod.* RETURNING id INTO new_id;
UPDATE product SET replaced_by_id = new_id WHERE id = prod.replaces_id;
FOR attr_val IN
SELECT * FROM attribute_value WHERE product_id = prod.replaces_id
LOOP
attr_val.product_id = new_id;
PERFORM update_attribute_value(attr_val);
END LOOP;
RETURN new_id;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
When I run update_product like this
select update_product(row('140613c5-3e83-4ae2-986e-5c6b824d5766', 0, '0ba5a6c8-2513-4163-a5bd-c460e10d2059', null, null,
'ce594f2c-87f9-497a-a0e5-7d3fbc4abd8a', null, 'aeabe6fe-b9e1-47e5-96a7-7bf16c56ddf4', 'Rotak 43 1', 'eaf6bea0-99c4-4759-8c38-d35b9ae11403', null,
'PENDING', null, null)::product);
I get the following error output:
ERROR: cannot assign non-composite value to a row variable SQL state: 42804 Context: PL/pgSQL function change_original_attribute_value() line 15 at assignment SQL statement "INSERT INTO attribute_value SELECT attr_val.* RETURNING attribute_value.id" PL/pgSQL function update_attribute_value(attribute_value) line 9 at SQL statement SQL statement "SELECT update_attribute_value(attr_val)" PL/pgSQL function update_product(product) line 17 at PERFORM
This error makes absolutely no sense to me in this context. Does anyone has an idea whats wrong in here, or could this be a bug?