From: Rasmus Aveskogh <rasmus@defero.se>
To: Chester Kustarz <chester@arbor.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Result set granularity..
Date: Mon, 29 Sep 2003 20:23:40 +0200 (CEST)
Message-ID: <34062.193.243.134.6.1064859820.squirrel@webmail.defero.se> (raw)
In-Reply-To: <Pine.BSO.4.44.0309291341430.9819-100000@detroit.arbor.net>
References: <59410.193.243.134.6.1064668289.squirrel@www.defero.se>
<Pine.BSO.4.44.0309291341430.9819-100000@detroit.arbor.net>
Well, there is a rowid and a rownum in Oracle. I don't remember which, but
one of them is significant to the current recieved result set only.
Hence; you can use it to manipulate and restrict the result set rather
than the data you're recieving from the tables. I.e. rownum 1 will always
be the first row in the result set recived by the select.
OID's in PostgreSQL, are, as you also mentioned, an object ID associated
with a certain object in the database and is therefore of no use in the
situation I mentioned in my earlier question. There is no predictability
regarding what OID a certain row in a table will have.
-ra
> there is a secret column called "oid" added to tables (unless you
> tell postgres not to.)
>
> select oid from my_table limit 10;
>
> oid
> ----------
> 77412298
> 77416344
> 77417164
> 77451631
> 77455960
> 77474124
> 77480067
> 77501568
> 77503239
> 77507326
> (10 rows)
>
> the oid is 32 unsigned int that is incremented for each object in the
> database. it is the postgres version of Oracle's rownum.
>
> search the docs for more info.
>
>
> On Sat, 27 Sep 2003, Rasmus Aveskogh wrote:
>> Since I went from Oracle to PostgreSQL I've been missing the "invisable"
>> column 'rownum'. I often used it to lower the granularity of my data.
>> For example, say I have a large table containing some sort of
>> statistical
>> data and want to plot a graph using it.
>> If the graph is 600 pixels wide I might as well lower the granularity of
>> my incoming data to 600 measure points before plotting.
>>
>> In Oracle I used to do this by using the modulus operator on the rownum
>> column as a restriction.
>>
>> SELECT <column> FROM <table> WHERE mod(rownum, 5) = 0;
>>
>> The query above would give me every fifth row of the original result set
>> and would save me from sending the data over my database connection and
>> do
>> the lowering of the granularity in the application.
>>
>> I have two questions, one dependent on the answer on the other one..
>>
>> 1) Is it possible to achieve this any other _easy_ way?
>> Perhaps it would be feasible to write a wrapper using a counter which
>> makes the SELECT and then return every fifth row to the SELECT calling
>> the
>> wrapper. But then I assume the data still has to "travel" one step which
>> puts on some overhead.
>>
>> 2) Would it be possible to add one more limit argument to the
>> non-standard
>> set of LIMIT and OFFET - a 'SCALE n' or 'GRANUL n' argument which would
>> return every n row of the initial result set. I think that would be
>> gladly accepted for folks working with statistical data.
>>
>> -ra
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: the planner will ignore your desire to choose an index scan if
>> your
>> joining column's datatypes do not match
>>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rasmus@defero.se, chester@arbor.net
Subject: Re: Result set granularity..
In-Reply-To: <34062.193.243.134.6.1064859820.squirrel@webmail.defero.se>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox