Re: Displaying two tables side by side
От | Rod Taylor |
---|---|
Тема | Re: Displaying two tables side by side |
Дата | |
Msg-id | 1092249149.16087.28.camel@jester обсуждение исходный текст |
Ответ на | Displaying two tables side by side (David Garamond <lists@zara.6.isreserved.com>) |
Список | pgsql-sql |
Depending on the size of your structures, something like the below may be significantly faster than the subselect alternative, and more reliable than the ctid alternative. CREATE TYPE result_info AS (a integer, b integer, c integer, d integer); CREATE OR REPLACE FUNCTION parallelselect() RETURNS SETOF result_info AS ' DECLARE res result_info%rowtype; ct1_found boolean DEFAULT true; ct2_found boolean DEFAULT true; ct1 CURSOR FOR SELECT a,b FROM t1; ct2 CURSOR FOR SELECT c,d FROM t2; BEGIN OPEN ct1; OPEN ct2; LOOP FETCH ct1 INTO res.a, res.b; ct1_found := FOUND; FETCH ct2 INTO res.c, res.d; ct2_found := FOUND; IF ct1_found OR ct2_found THEN RETURN NEXT res; ELSE EXIT; END IF; END LOOP; RETURN; END; ' LANGUAGE plpgsql; SELECT * FROM parallelselect() AS tab;a | b | c | d ---+---+---+---2 | 2 | 4 | 53 | 5 | 7 | 34 | 7 | 3 | 29 | 0 | 1 | 1 | | 2 | 0 (5 rows) On Wed, 2004-08-11 at 10:11, David Garamond wrote: > How can you display two tables side by side? Example: > > > select * from t1; > a | b > ---+--- > 2 | 2 > 3 | 5 > 4 | 7 > 9 | 0 > > > select * from t2; > c | d > ---+--- > 4 | 5 > 7 | 3 > 3 | 2 > 1 | 1 > 2 | 0 > > Intended output: > a | b | c | d > ---+---+---+--- > 2 | 2 | 4 | 5 > 3 | 5 | 7 | 3 > 4 | 7 | 3 | 2 > 9 | 0 | 1 | 1 > | | 2 | 0 > > Each table has no keys (and no OIDs). Order is not important, but each > row from each table needs to be displayed exactly once. > > -- > dave > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
В списке pgsql-sql по дате отправления: