select into composite type / return
От | Gary Stainburn |
---|---|
Тема | select into composite type / return |
Дата | |
Msg-id | 39dcfab9-4eb2-382b-9186-baf722ca56a0@ringways.co.uk обсуждение исходный текст |
Ответы |
Re: select into composite type / return
|
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: