Subject: Re: Displaying two tables side by side
From: Rod Taylor <pg@rbt.ca>
To: David Garamond <lists@zara.6.isreserved.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <411A2926.9020500@zara.6.isreserved.com>
References: <411A2926.9020500@zara.6.isreserved.com>
Content-Type: text/plain
Message-Id: <1092249149.16087.28.camel@jester>
Mime-Version: 1.0
Date: Wed, 11 Aug 2004 14:32:30 -0400
Content-Transfer-Encoding: 7bit

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 | 5
 3 | 5 | 7 | 3
 4 | 7 | 3 | 2
 9 | 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