Re: select into composite type / return
От | Gary Stainburn |
---|---|
Тема | Re: select into composite type / return |
Дата | |
Msg-id | 7af5d693-a6a3-d3d4-050a-0898aad8bd77@ringways.co.uk обсуждение исходный текст |
Ответ на | Re: select into composite type / return (Gary Stainburn <gary.stainburn@ringways.co.uk>) |
Ответы |
Re: select into composite type / return
(Torsten Grust <torsten.grust@gmail.com>)
|
Список | pgsql-sql |
I now have the working functions. The first accepts 7 arguments and returns a composite type of the calculations breakdown. The second takes a single argument and retrieves the 7 arguments from a table before calling the first argument. What I can't get my head round is how I can use these functions to return a setof breakdowns. All I can get is thebreakdown returned as a single column. All advice welcome. users=# select * from do_breakdown(1); f1 | f2 | f3 | f4 | f5 | f6 ------+------+------+------+------+------ 1.00 | 2.00 | 3.00 | 4.00 | 5.00 | 6.00 (1 row) users=# select * from sessions; id | v1 | v2 | v3 | v4 | v5 | v6 | v7 ----+-------+-------+-------+-------+-------+-------+------- 1 | 1.00 | 2.00 | 3.00 | 4.00 | 5.00 | 6.00 | 7.00 2 | 11.00 | 12.00 | 13.00 | 14.00 | 15.00 | 16.00 | 17.00 3 | 21.00 | 22.00 | 23.00 | 24.00 | 25.00 | 26.00 | 27.00 (3 rows) users=# select id, do_breakdown(id) from sessions where id in (1,3); id | do_breakdown ----+--------------------------------------- 1 | (1.00,2.00,3.00,4.00,5.00,6.00) 3 | (21.00,22.00,23.00,24.00,25.00,26.00) (2 rows) users=# 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 table sessions ( ID int4 not null primary key, 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) ); insert into sessions values (1,1,2,3,4,5,6,7),(2,11,12,13,14,15,16,17),(3,21,22,23,24,25,26,27); create or replace 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 D.f1=v1; D.f2=v2; D.f3=v3; D.f4=v4; D.f5=v5; D.f6=v6; return D; END; $$ LANGUAGE PLPGSQL; create or replace function do_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; RETURN do_breakdown(v.v1,v.v2,v.v3,v.v4,v.v5,v.v6,v.v7); END; $$ LANGUAGE PLPGSQL;
В списке pgsql-sql по дате отправления: