42883 ERROR: operator does not exist: record -> unknown

Question

How I call the function:

SELECT opportunity_price_total(($$
        [{"value_price": 10, "value_period": {"value_period_id": 1}},{"value_price": 1, "value_period": {"value_period_id": 2}},{"value_price": 10, "value_period": {"value_period_id": 4}}]
    $$)::jsonb);

opportunity_price_total function:

CREATE FUNCTION opportunity_price_total(prices jsonb) RETURNS BIGINT
    LANGUAGE plpgsql
AS
    $$
        DECLARE
            price record;
            total_annually BIGINT = 0;
        BEGIN
            FOR price IN SELECT * FROM jsonb_array_elements(prices) LOOP
                total_annually := total_annually + opportunity_price_annually((price->'value_price')::bigint,(price->'value_period'->>'value_period_id')::int);
            END LOOP;
            RETURN total_annually;
        END
    $$;

the output I get is:

[42883] ERROR: operator does not exist: record -> unknown

any ideas how should I approach for solution?

Are you sure the exception is occurring in one of the lookups in this function, as opposed to something inside `opportunity_price_annually()`? — AdamKG, Apr 13 '21 at 13:01

score 2 · Accepted Answer · answered Apr 13 '21 at 13:12

You cannot use the -> operator on a record (composite type). You'd either have to get the appropriate column first. It is better to use a jsonb for the loop valiable:

DECLARE
    price jsonb;
    total_annually BIGINT := 0;
BEGIN
    FOR price IN
        SELECT x FROM jsonb_array_elements(prices) AS j(x)
    LOOP
        total_annually := total_annually +
               opportunity_price_annually(
                   (price->'value_price')::bigint,
                   (price->'value_period'->>'value_period_id')::int
               );
    END LOOP;
    RETURN total_annually;
END

Thank you for your time @Laurenz Albe – saimcan Apr 13 '21 at 13:21 — saimcan, Apr 13 '21 at 13:21

42883 ERROR: operator does not exist: record -> unknown

1 Answers1