I've added another function, partly to aid debugging, partly to test the next part of the project.
The idea is simple. select the results of the calculation into a local variable and then process it. However, I can't get the select to work. The failure message relates to the "select into D" line.
gary=# select * from read_breakdown(1); ERROR: invalid input syntax for type numeric: "(1.00,2.00,3.00,4.00,5.00,6.00)" CONTEXT: PL/pgSQL function read_breakdown(integer) line 12 at SQL statement gary=#
create or replace function read_breakdown(vID int4) RETURNS breakdown AS $$ DECLARE v RECORD; D breakdown; BEGIN IF vID IS NULL THEN RETURN NULL; END IF; select into v * from sessions s where s.ID = vID; IF NOT FOUND THEN RAISE NOTICE 'breakdown: % not found',vID; RETURN NULL; END IF; select into D do_breakdown(v.v1,v.v2,v.v3,v.v4,v.v5,v.v6,v.v7); IF NOT FOUND THEN RAISE NOTICE 'breakdown: % calculation failed',vID; RETURN NULL; END IF; RAISE NOTICE 'read_breakdown: f1=%',D.f1; RAISE NOTICE 'read_breakdown: f2=%',D.f2; RAISE NOTICE 'read_breakdown: f3=%',D.f3; RAISE NOTICE 'read_breakdown: f4=%',D.f4; RAISE NOTICE 'read_breakdown: f5=%',D.f5; RAISE NOTICE 'read_breakdown: f6=%',D.f6; RETURN D; END; $$ LANGUAGE PLPGSQL;
select into D do_breakdown(v.v1,v.v2,v.v3,v.v4,v.v5,v.v6,v.v7);
this line is wrong
you should to use select * from do_breakdown(..) into D
note - plpgsql is case insensitive language - all identifiers are transformed to lower case form - so using identifiers like D is not a good idea.