Re: Displaying two tables side by side

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