I have a function that takes 7 numerical inputs, performs calculations,
and then returns a composite type.
create type breakdown as
f1 numeric(9,2),
f2 numeric(9,2),
f3 numeric(9,2),
f4 numeric(9,2),
f5 numeric(9,2),
f6 numeric(9,2)
);
create function do_breakdown(
v1 numeric(9,2),
v2 numeric(9,2),
v3 numeric(9,2),
v4 numeric(9,2),
v5 numeric(9,2),
v6 numeric(9,2),
v7 numeric(9,2)
) returns breakdown as $$
DECLARE
D breakdown;
BEGIN
-- calculate breakdown
return D;
END;
$$
LANGUAGE PLPGSQL;
This works great, returning one row with the separate columns.
I now want to set up another function which will take a key, retrieve
the arguments from a table, and call the first function. The problem is
that I can't get the syntax correct to return the composite type. I
have tried
create function do_breakdown(key text) returns breakdown as $$
DECLARE
v RECORD;
BEGIN
select into v * from stored s where key s.key = key;
RETURN do_breakdown(v.f1,v.f2,v.f3,v.f4,v.f5,v.f6);
END;
$$
LANGUAGE PLPGSQL;
but it returns the whole thing as a single column. Adding a typecase
didn't help.
I have also tried
create function do_breakdown(key text) returns breakdown as $$
DECLARE
v RECORD;
D breakdown;
BEGIN
select into v * from stored s where key s.key = key;
select into D * from do_breakdown(v.f1,v.f2,v.f3,v.f4,v.f5,v.f6);
RETURN D;
END;
$$
LANGUAGE PLPGSQL;
but that also returns everything as a single column. Any help would be
appreciated.
Gary