Skip site navigation (1) Skip section navigation (2)

Re: How to "paste two tables side-by-side"?

From: "Kynn Jones" <kynnjo(at)gmail(dot)com>
To: "pgsql-general General" <pgsql-general(at)postgresql(dot)org>
Subject: Re: How to "paste two tables side-by-side"?
Date: 2008-02-27 13:22:24
Message-ID: c2350ba40802270522s6ace8045recacb2a92afda30a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
On Wed, Feb 27, 2008 at 7:39 AM, Kynn Jones <kynnjo(at)gmail(dot)com> wrote:

> Suppose I have two tables, A and B, with k(A) and k(B) columns
> respectively, and let's assume to begin with that they have the same number
> of rows r(A) = r(B) = r.
> What's the simplest way to produce a table C having r rows and k(A) + k(B)
> columns, and whose i-th row consists of the k(A) columns of the i-th row of
> A followed by the k(B) columns of the i-th row of B (for i = 1,...,r)?  (By
> "i-th row of A" I mean the i-th row of the listing one would get from
> "SELECT * FROM A", and likewise for B.)
>

Expanding on my own post here, it occurred to me that it would be very nice
to have a function (say) index, that, when used in a SELECT list, would
yield the position in the corresponding table of the current row.  E.g. the
expression

  SELECT index(*) FROM A;

would produce the same table as

  SELECT generate_series( 1, r(A) );

It would also be useful to have a "subscripting function" s (which may be
regarded as somewhat of the inverse of index()) that, given a table
expression E, and an "index expression" I (which could be a single index or
range, or a list of such), will return the table consisting of the rows in E
designated by the indices in I).  For example, either one these queries

  SELECT s( A, 1, 2, 3 );
  SELECT * FROM s( A, 1, 2, 3 );

would produce the same table as

  SELECT * FROM A LIMIT 3;

Does anything like index() or s() exist?  If not, are there other functions
that may be useful in an implementation of index() or s()?

Thanks!

Kynn

In response to

Responses

pgsql-general by date

Next:From: Jan ChristensenDate: 2008-02-27 13:41:42
Subject: PostgreSQL-installation-problem on Windows XP Home edition
Previous:From: Alvaro HerreraDate: 2008-02-27 13:20:29
Subject: Re: autovacuum not freeing up unused space on 8.3.0

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group