On Feb 27, 2008, at 7:22 AM, Kynn Jones wrote:
On Wed, Feb 27, 2008 at 7:39 AM, Kynn Jones <kynnjo@xxxxxxxxx> 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@xxxxxxxxxx
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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
