0

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?

0

1 Answer 1

1

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.