Initialize multiple variables as records in postgresql stored procedure

Question

I have a stored procedure where I declare three variables as records and initialize them later as below:

CREATE OR REPLACE FUNCTION calculateUnitPrice() 
RETURNS VOID AS
$$
DECLARE
      amenities       RECORD;
      paramValues RECORD;
      logsumAccessebility RECORD;
      propertyType   integer;
      unitPrice      float;
      freehold       integer;
      tazId          bigint;
      unit            RECORD;
BEGIN
     FOR unit IN (SELECT * from main2012.fm_unit_res)
LOOP
    amenities := getAmenitiesById(unit.sla_address_id);
    tazId := toBigint(amenities.taz_id);
    logsumAccessebility := getLogsumByTazId(tazId);
    propertyType := getPropertyTypeFromUnitType(unit.unit_type);
    paramValues := getParamValuesByPropertyType(propertyType);
    freehold := 0;
    unitPrice := paramValues.intercept + (paramValues.floor_area * ln(unit.floor_area)) + (paramValues.freehold * freehold) + (paramValues.logsum_accessebility * logsumAccessebility.accessibility);
    UPDATE main2012.fm_unit_res SET rent = unitPrice WHERE fm_unit_id = unit.fm_unit_id;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;

But when I run the function I am getting an error like this:

    ERROR: record "paramvalues" is not assigned yet
    SQL state: 55000
    Detail: The tuple structure of a not-yet-assigned record is indeterminate.

Context: PL/pgSQL function calculateunitprice() line 20 at assignment

Please give me your ideas. Am I doing anything wrong here (syntax) or is there a limit in the number of records I can initialize within a stored procedure?

maybe cos you assign hparamValues := instead of paramValues :=? :) — Vao Tsun
– Vao Tsun, Commented Aug 19, 2015 at 5:57
Thanks for the reply @VaoTsun. Sorry, it was a typing mistake. It is not the problem. I have corrected it in the question. — gishara
– gishara, Commented Aug 19, 2015 at 6:02
add raise info '%',getPropertyTypeFromUnitType(unit.unit_type);raise info '%',getPropertyTypeFromUnitType(unit.unit_type); before declaring paramValues, then run function and tell the last value of raised info before error?.. — Vao Tsun
– Vao Tsun, Commented Aug 19, 2015 at 6:21
Thanks @VaoTsun. The last value before error was 0 which explains that no paramValues were selected. I have to rewrite the function to handle this I guess. Thanks again. — gishara
– gishara, Commented Aug 19, 2015 at 6:31

Vao Tsun · Accepted Answer · 2015-08-19 06:33:26Z

1

Debug the output, by adding

raise info '%',getPropertyTypeFromUnitType(unit.unit_type); raise info '%',getPropertyTypeFromUnitType(unit.unit_type); before declaring paramValues

answered Aug 19, 2015 at 6:33

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

Collectives™ on Stack Overflow

Initialize multiple variables as records in postgresql stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related