Postgres “cannot assign non-composite value to a row variable” error assigning a composite value

Question

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?

Roland Roland · Accepted Answer · 2016-02-01T11:21:24

I found the error. The problem was inside the function change_original_attribute_value() that is triggered on insert of a product. In there a function was called that returns a UUID, but the type it was assigned to was a rowtype.

Postgres “cannot assign non-composite value to a row variable” error assigning a composite value

1 Answers