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

On Feb 27, 2008, at 7:22 AM, Kynn Jones wrote:

> 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()?

If I'm understanding what you're asking for here, no it doesn't and
it doesn't make sense for it to. Table rows are inherently
unordered, to get an ordering you supply and ORDER BY clause. To get
a certain position in that ordering you take on an OFFSET and/or
LIMIT clause.

Another way of putting it is that at the base table level, rows do
not have position within the table. As soon as you update a row, it
"moves" as the original row is deleted and a new one is inserted.
Once vacuum does it's job, then newly inserted rows are inserted over
previously deleted rows, i.e in the "middle" of the table.

Erik Jones

DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com