Re: Does Postgresql have a similar pseudo-column "ROWNUM" as

Hi:

Oracle has a pseudo-column "ROWNUM" to return the sequence number in which a row was returned when selected from a table. The first row ROWNUM is 1, the second is 2, and so on.

Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If so, we can write the following query:

select *
from (select RowNum, pg_catalog.pg_proc.*
from pg_catalog.pg_proc) inline_view
where RowNum between 100 and 200;

Thanks,
Dennis

On Thu, 2005-05-12 at 14:07, Dennis(dot)Jiang(at)thomson(dot)com wrote:
> Hi:
>
> Oracle has a pseudo-column "ROWNUM" to return the sequence number in which a row was returned when selected from a table. The first row ROWNUM is 1, the second is 2, and so on.
>
> Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If so, we can write the following query:
>
> select *
> from (select RowNum, pg_catalog.pg_proc.*
> from pg_catalog.pg_proc) inline_view
> where RowNum between 100 and 200;

You can get a functional equivalent with a temporary sequence:

create temp sequence rownum;
select *, nextval('rownum') as rownum from sometable;

On Thu, May 12, 2005 at 13:07:00 -0600,
Dennis(dot)Jiang(at)thomson(dot)com wrote:
> Hi:
>
> Oracle has a pseudo-column "ROWNUM" to return the sequence number in which a row was returned when selected from a table. The first row ROWNUM is 1, the second is 2, and so on.
>
> Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If so, we can write the following query:

No.

>
> select *
> from (select RowNum, pg_catalog.pg_proc.*
> from pg_catalog.pg_proc) inline_view
> where RowNum between 100 and 200;

You could use LIMIT and OFFSET to get the values from the table for the
100th through 200th rows (though typically you want to add an ORDER BY
clause). You could have you application supply the rownum column values.

On Thu, May 12, 2005 at 01:07:00PM -0600, Dennis(dot)Jiang(at)thomson(dot)com wrote:
>
> Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If
> so, we can write the following query:

No. What is the purpose of your query? You could use ORDER BY and
LIMIT..OFFSET to do what you want. I think.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
The whole tendency of modern prose is away from concreteness.
--George Orwell

Andrew Sullivan escreveu:
> On Thu, May 12, 2005 at 01:07:00PM -0600, Dennis(dot)Jiang(at)thomson(dot)com wrote:
>
>>Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If
>>so, we can write the following query:
>
>
> No. What is the purpose of your query? You could use ORDER BY and
> LIMIT..OFFSET to do what you want. I think.

The problem is probably speed. I have done a lot of tests, and when
OFFSET gets to a few thousands on a multimega-recs database, it gets
very very slow... Is there any other to work around that?

Alain

On Tue, 2005-05-17 at 14:48 -0300, Alain wrote:
>
> Andrew Sullivan escreveu:
> > On Thu, May 12, 2005 at 01:07:00PM -0600, Dennis(dot)Jiang(at)thomson(dot)com wrote:
> >
> >>Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If
> >>so, we can write the following query:
> >
> >
> > No. What is the purpose of your query? You could use ORDER BY and
> > LIMIT..OFFSET to do what you want. I think.
>
> The problem is probably speed. I have done a lot of tests, and when
> OFFSET gets to a few thousands on a multimega-recs database, it gets
> very very slow...

is there not a similar loss of speed using ROWNUM on oracle?

> ... Is there any other to work around that?

if you are ordering by a unique key, you can use the key value
in a WHERE clause.

select ... where ukey>? order by ukey limit 100 offset 100;

(the ? is placeholder for the last value of ukey returned
from previous select)

gnari

Hi

I'm trying to insert encrypted data into the database and I'm noticing
error dealing with quotes. Below is the error print out...

suggestions and/or at least point me in the direction to find a solution,

Thanks,
J

INSERT INTO sample.users (user_name, first_name) VALUES
('jokers', '=ïµiF!¶6(ÖŸã¾óˆÌ‘'-Iw‰iDÖiJÐÿ† %')

Warning: pg_query() [function.pg-query]: Query failed: ERROR:
unterminated quoted string at or near "'=ïµi" at character 68 in
/usr/local/apache2/htdocs/php/5/Theirry_DB.php on line 162

On Tue, 2005-05-17 at 13:15, Postgres Admin wrote:
> Hi
>
> I'm trying to insert encrypted data into the database and I'm noticing
> error dealing with quotes. Below is the error print out...
>
> suggestions and/or at least point me in the direction to find a solution,
>
> Thanks,
> J
>
>
>
> INSERT INTO sample.users (user_name, first_name) VALUES
> ('jokers', '=ïµiF!¶6(ÖŸã¾óˆÌ‘'-Iw‰iDÖiJÐÿ† %')
>
> Warning: pg_query() [function.pg-query]: Query failed: ERROR:
> unterminated quoted string at or near "'=ïµi" at character 68 in
> /usr/local/apache2/htdocs/php/5/Theirry_DB.php on line 162

Use a bytea field and use pg_escape_bytea() to prepare the data for
insertion.

Scott Marlowe wrote:

>
>Use a bytea field and use pg_escape_bytea() to prepare the data for
>insertion.
>
>
>
Thanks Scott, I will try it now.

J

>>>
>>>No. What is the purpose of your query? You could use ORDER BY and
>>>LIMIT..OFFSET to do what you want. I think.
>>
>>The problem is probably speed. I have done a lot of tests, and when
>>OFFSET gets to a few thousands on a multimega-recs database, it gets
>>very very slow...
>
>
> is there not a similar loss of speed using ROWNUM on oracle?
>
>
>>... Is there any other to work around that?
>
>
> if you are ordering by a unique key, you can use the key value
> in a WHERE clause.
>
> select ... where ukey>? order by ukey limit 100 offset 100;
>
> (the ? is placeholder for the last value of ukey returned
> from previous select)

I tried that. It does not work in the generic case: 6 MegaRec, telephone
listing, alphabetical order. The problem is that somewhere there is a
single user with too many entries (over 1000). I even tried to filter
the repetitions, but somewhere I get stuck if one guy has too mny
entries (one for each phone number).

I tried using both the name and the primary key (with a combined index),
to get faster to the record I want, but I was not sucessfull in building
a where clause.

I would appreciate any help, in fact this is my primary reason for
joining this list ;-)

Alain

On 2005-05-17 14:15:53 -0400 (Tue, May), Postgres Admin wrote:
> Hi
>
> I'm trying to insert encrypted data into the database and I'm noticing
> error dealing with quotes. Below is the error print out...
>
> suggestions and/or at least point me in the direction to find a solution,
>
> Thanks,
> J
>
> INSERT INTO sample.users (user_name, first_name) VALUES
> ('jokers', '=??iF!?6(Ö????ó???'-Iw?iDÖiJ??? %')
>
> Warning: pg_query() [function.pg-query]: Query failed: ERROR:
> unterminated quoted string at or near "'=??i" at character 68 in
> /usr/local/apache2/htdocs/php/5/Theirry_DB.php on line 162

You should pass the encrypted data thru pg_escape_string() function.
You surely should do it with almost EVERY string that you put as an
argument in a query, especially if it comes from 'outside'.

$query = "INSERT INTO .... ('jokers','" . pg_escape_string($encrypted) . "');" ;

--
bashian roulette:
$ ((RANDOM%6)) || rm -rf ~

On Tue, May 17, 2005 at 03:43:32PM -0300, Alain wrote:
>
> I tried using both the name and the primary key (with a combined index),
> to get faster to the record I want, but I was not sucessfull in building
> a where clause.
>
> I would appreciate any help, in fact this is my primary reason for
> joining this list ;-)

Well, then, table schemas, data distribution, EXPLAIN and EXPLAIN
ANALYSE output, and some statement of what you're trying to get out
is likely what we need to see.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie

On Tue, 2005-05-17 at 15:43 -0300, Alain wrote:
> [how to solve the get next 100 records problem]

I am assuming this is for a web like interface, in other words that
cursors are not applicable

> > [me]
> > if you are ordering by a unique key, you can use the key value
> > in a WHERE clause.
> >
> > select ... where ukey>? order by ukey limit 100 offset 100;
> >
> > (the ? is placeholder for the last value of ukey returned
> > from previous select)
>
> I tried that. It does not work in the generic case: 6 MegaRec, telephone
> listing, alphabetical order. The problem is that somewhere there is a
> single user with too many entries (over 1000). I even tried to filter
> the repetitions, but somewhere I get stuck if one guy has too mny
> entries (one for each phone number).
>
> I tried using both the name and the primary key (with a combined index),
> to get faster to the record I want, but I was not sucessfull in building
> a where clause.

lets say pkey is your primary key and skey is your sort key, and
there exists an index on (skey,pkey)

your first select is
select ... from tab ORDER by skey,pkey LIMIT 100;

your subsequent selects are
select ... from tab WHERE skey>skey_last
OR (skey=skey_last AND pkey>pkey_last)
ORDER BY skey,pkey
LIMIT 100 OFFSET 100;

> I would appreciate any help, in fact this is my primary reason for
> joining this list ;-)

gnari

> your subsequent selects are
> select ... from tab WHERE skey>skey_last
> OR (skey=skey_last AND pkey>pkey_last)
> ORDER BY skey,pkey
> LIMIT 100 OFFSET 100;

why offset ?
you should be able to use the skey, pkey values of the last row on the
page to show the next page, no need for offset then.

On Wed, 2005-05-18 at 00:13 +0200, PFC wrote:
> > your subsequent selects are
> > select ... from tab WHERE skey>skey_last
> > OR (skey=skey_last AND pkey>pkey_last)
> > ORDER BY skey,pkey
> > LIMIT 100 OFFSET 100;
>
> why offset ?
> you should be able to use the skey, pkey values of the last row on the
> page to show the next page, no need for offset then.

of course you are right. the WHERE clause is supposed to replace
the OFFSET. too much cut and pasting without thinking and testing.

gnari

Ragnar Hafstað escreveu:
>>[how to solve the get next 100 records problem]
>
>>I tried that. It does not work in the generic case: 6 MegaRec, telephone
>>listing, alphabetical order.
>
> lets say pkey is your primary key and skey is your sort key, and
> there exists an index on (skey,pkey)
>
> your first select is
> select ... from tab ORDER by skey,pkey LIMIT 100;
>
> your subsequent selects are
> select ... from tab WHERE skey>skey_last
> OR (skey=skey_last AND pkey>pkey_last)
> ORDER BY skey,pkey
> LIMIT 100 OFFSET 100;

I tied that, it is veeery slow, probably due to the OR operand :(

BUT, I think that this is close to a final solution, I made some
preliminary test ok. Please tell me what you think about this.

Fisrt let's state that I am reading records to put on a screen (in a
Table/Grid). I separated the problem is *3* parts

-first select is as above:
select ... from tab ORDER by skey,pkey LIMIT 100;

-second method for next 100:
select ... from tab WHERE skey>=skey_last
ORDER BY skey,pkey
LIMIT 100;
but here I test for repetitions using pkey and discard them

-now if I get all repetitions or the last 100 have the same skey with
the second method, I use
select ... from tab WHERE skey=skey_last AND pkey>pkey_last
ORDER BY skey,pkey
LIMIT 100;
until I get an empty response, then I go back to the second method.

All queries are extremely fast with 6000000 records and it looks like
the few redundant or empty queries (but very fast) will not be a problem.

What is your opinion about this (apart that it is a bit complex :) ??

Alain

On Tue, 2005-05-17 at 23:16 -0300, Alain wrote:
>
> Ragnar Hafstað escreveu:
> >>[how to solve the get next 100 records problem]

> BUT, I think that this is close to a final solution, I made some
> preliminary test ok. Please tell me what you think about this.
>
> Fisrt let's state that I am reading records to put on a screen (in a
> Table/Grid). I separated the problem is *3* parts
>
> -first select is as above:
> select ... from tab ORDER by skey,pkey LIMIT 100;
>
> -second method for next 100:
> select ... from tab WHERE skey>=skey_last
> ORDER BY skey,pkey
> LIMIT 100;
> but here I test for repetitions using pkey and discard them
>
> -now if I get all repetitions or the last 100 have the same skey with
> the second method, I use
> select ... from tab WHERE skey=skey_last AND pkey>pkey_last
> ORDER BY skey,pkey
> LIMIT 100;
> until I get an empty response, then I go back to the second method.

if your distribution is such that those skeys that have > 100 records
tend to have a lot more, you might have a higher limit for this case.

> All queries are extremely fast with 6000000 records and it looks like
> the few redundant or empty queries (but very fast) will not be a problem.
>
> What is your opinion about this (apart that it is a bit complex :) ??

looks fine

gnari

Hi,

On 5/17/05, Postgres Admin <postgres(at)productivitymedia(dot)com> wrote:
> I'm trying to insert encrypted data into the database and I'm noticing
> error dealing with quotes. Below is the error print out...
>
> suggestions and/or at least point me in the direction to find a solution,
>
> INSERT INTO sample.users (user_name, first_name) VALUES
> ('jokers', '=ïµiF!¶6(֟㍾óˆÌ''-Iw‰iDÖiJÐÿ† %')
If you don't use parameters, you need to escape the data to place in
an SQL query command. But this may cost so much on the CPU and RAM
side if your will be escaped data is enough long to exhaust other
processes. (For example, it's not so feasible to unescape a media file
while inserting.)

At the moment, current stable releases of PHP doesn't support
parameter usage, but it was committed to CVS in revision 1.315. If
you'll need these kind of escape functions so much, I'd encourage you
to patch your php/ext/pgsql/pgsql.c with the one in CVS tree. After
patch, you'll be able to use pg_query_params, pg_prepare, pg_execute,
pg_send_query_params and pg_send_prepare; namely, every possible
parameter supported function.

Regards.

Hi Ragnar (and others),

I found something that is both fast and simple (program side):

Allways assuming that: pkey is a primary key and skey is a sort key, and
there exists an index on (skey,pkey)

first select is
select ... from tab ORDER by skey,pkey LIMIT 100;

subsequent selects are
(select ... from tab WHERE skey=skey_last AND pkey>pkey_last
ORDER BY skey,pkey LIMIT 100)
UNION
(select ... from tab WHERE skey>skey_last
ORDER BY skey,pkey LIMIT 100)
ORDER BY skey,pkey LIMIT 100;

The catch is that if the first select would have more than 100 records
and was limited to 100, the second select's data is completeply
discarted by the 3rd limit!

The only strange thing is that without the 3rd order by, the order is
wrong. I didn't expect it because each select is created ordered. Is it
expected that UNION mixes it all up? (using postgre 7.4.1)

The 3rd order by is not indexed, but it operates in a memory table of no
more than 200 so it is fast too.

Please comment on this. I tested and it worked but I really new to sql
and I feel insecure...

Thanks,
Alain

>>
>>>>[how to solve the get next 100 records problem]
>
>>BUT, I think that this is close to a final solution, I made some
>>preliminary test ok. Please tell me what you think about this.
>>
>>Fisrt let's state that I am reading records to put on a screen (in a
>>Table/Grid). I separated the problem is *3* parts
>>
>>-first select is as above:
>>select ... from tab ORDER by skey,pkey LIMIT 100;
>>
>>-second method for next 100:
>>select ... from tab WHERE skey>=skey_last
>> ORDER BY skey,pkey
>> LIMIT 100;
>>but here I test for repetitions using pkey and discard them
>>
>>-now if I get all repetitions or the last 100 have the same skey with
>>the second method, I use
>>select ... from tab WHERE skey=skey_last AND pkey>pkey_last
>> ORDER BY skey,pkey
>> LIMIT 100;
>>until I get an empty response, then I go back to the second method.
>
>
> if your distribution is such that those skeys that have > 100 records
> tend to have a lot more, you might have a higher limit for this case.
>
>
>
>>All queries are extremely fast with 6000000 records and it looks like
>>the few redundant or empty queries (but very fast) will not be a problem.
>>
>>What is your opinion about this (apart that it is a bit complex :) ??
>
>
>
> looks fine
>
> gnari
>
>
>
>

> The only strange thing is that without the 3rd order by, the order is
> wrong. I didn't expect it because each select is created ordered. Is it
> expected that UNION mixes it all up? (using postgre 7.4.1)

That's because UNION removes duplicates, which it will probably doing
using a hash (EXPLAIN ANALYZE is your friend).
Use UNION ALL because your WHERE condition allows no duplicates anyway.
UNION ALL preserves the order.
So you can get the LIMIT out of the subqueries and put it around the
UNION ALL.

On Wed, 2005-05-18 at 12:42 -0300, Alain wrote:
> I found something that is both fast and simple (program side):
> ...
> subsequent selects are
> (select ... from tab WHERE skey=skey_last AND pkey>pkey_last
> ORDER BY skey,pkey LIMIT 100)
> UNION
> (select ... from tab WHERE skey>skey_last
> ORDER BY skey,pkey LIMIT 100)
> ORDER BY skey,pkey LIMIT 100;
> ...
> The only strange thing is that without the 3rd order by, the order is
> wrong. I didn't expect it because each select is created ordered. Is it
> expected that UNION mixes it all up? (using postgre 7.4.1)

ORDER BY on subselects are not garanteed by SQL to have any effect,
but Postgres tends to do what you want when possible and not
detrimental to performance.
In this case, Postgres would probably have kept the order had
you used UNION ALL

a plain UNION implies DISTINCT, which Postgres is free to
implement any way it wants, possibly destroying the order

in this case a UNION ALL is appropriate, as you know that
the 2 selects do not overlap. possibly, a future version
of the planner will be able to detect this.

in any case, the last ORDER BY LIMIT does not cost much, and it
protects you against implementation changes, and limits
the result to 100 records, which might be what you want.

> Please comment on this. I tested and it worked but I really new to sql
> and I feel insecure...

it's good.

gnari

alainm(at)pobox(dot)com (Alain) writes:
> Andrew Sullivan escreveu:
>> On Thu, May 12, 2005 at 01:07:00PM -0600, Dennis(dot)Jiang(at)thomson(dot)com wrote:
>>
>>>Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If
>>>so, we can write the following query:
>> No. What is the purpose of your query? You could use ORDER BY and
>> LIMIT..OFFSET to do what you want. I think.
>
> The problem is probably speed. I have done a lot of tests, and when
> OFFSET gets to a few thousands on a multimega-recs database, it gets
> very very slow... Is there any other to work around that?

The other way to do this would involve creating a cursor against the
table, and using suitable FETCHes to grab the portions that you
needed.

In practice, this has seemed to be the relevant answer to what the
application developer actually wanted.

The common "use case" where I see it is in a web application where
they discover that there are 800K records, and the user only wants a
screenful at a time.

Establishing a cursor, and having the web app jump around on it, seems
to be the right answer. (Whether it's reasonably implementable by the
developers may be another question, but that's allowed to be a
separate question ;-).)
--
(format nil "~S(at)~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/>

On May 18, 2005, at 3:52 PM, Chris Browne wrote:

> alainm(at)pobox(dot)com (Alain) writes:
>> Andrew Sullivan escreveu:
>>> On Thu, May 12, 2005 at 01:07:00PM -0600, Dennis(dot)Jiang(at)thomson(dot)com
>>> wrote:
>>>
>>>> Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If
>>>> so, we can write the following query:
>>> No. What is the purpose of your query? You could use ORDER BY and
>>> LIMIT..OFFSET to do what you want. I think.
>>
>> The problem is probably speed. I have done a lot of tests, and when
>> OFFSET gets to a few thousands on a multimega-recs database, it gets
>> very very slow... Is there any other to work around that?
>
> The other way to do this would involve creating a cursor against the
> table, and using suitable FETCHes to grab the portions that you
> needed.
>
> In practice, this has seemed to be the relevant answer to what the
> application developer actually wanted.
>
> The common "use case" where I see it is in a web application where
> they discover that there are 800K records, and the user only wants a
> screenful at a time.
>
> Establishing a cursor, and having the web app jump around on it, seems
> to be the right answer. (Whether it's reasonably implementable by the
> developers may be another question, but that's allowed to be a
> separate question ;-).)

In a web app, I doubt that cursors can be useful because of the
stateless nature of web interaction. I'd love to hear otherwise,
but....

Sean

On Tue, 17 May 2005 12:01:03 -0500, Scott Marlowe wrote
> On Thu, 2005-05-12 at 14:07, Dennis(dot)Jiang(at)thomson(dot)com wrote:
> > Hi:
> >
> > Oracle has a pseudo-column "ROWNUM" to return the sequence
> > number in which a row was returned when selected from a table.
> > The first row ROWNUM is 1, the second is 2, and so on.
> >
> > Does Postgresql have a similar pseudo-column "ROWNUM" as
> > Oracle? If so, we can write the following query:
> >
> > select *
> > from (select RowNum, pg_catalog.pg_proc.*
> > from pg_catalog.pg_proc) inline_view
> > where RowNum between 100 and 200;
>
> You can get a functional equivalent with a temporary sequence:
>
> create temp sequence rownum;
> select *, nextval('rownum') as rownum from sometable;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

Scott,

I realize that this thread went off in another direction however your
suggestion proved very helpful for a problem that I was trying to solve. I
wanted the row number of a set returned by a function. Here is a chopped
version of the function that I wrote.

CREATE OR REPLACE FUNCTION func_bom(integer, integer)
RETURNS SETOF func_bom AS
$BODY$
DECLARE
v_number ALIAS FOR $1;
v_line ALIAS FOR $2;
v_type varchar(8);
r_row interface.func_so_line_bom%rowtype;
BEGIN
SELECT tbl_item.item_type INTO v_type
FROM tbl_line_item
JOIN tbl_item
ON tbl_line_item.item_id = tbl_item.id
WHERE tbl_line_item.number = v_number
AND tbl_line_item.line = v_line;
IF v_type = 'ASY' THEN
CREATE TEMP SEQUENCE row_number
INCREMENT BY 1
START WITH 1;
FOR r_row IN SELECT tbl_line_item.number,
tbl_line_item.line,
nextval('row_number') AS subline,
tbl_assembly.quantity AS bom_quantity,
tbl_assembly.component_id AS bom_item_id,
tbl_item.item_type AS bom_item_type,
tbl_item.description AS bom_item_description
FROM tbl_line_item
LEFT JOIN tbl_assembly
ON ( tbl_line_item.item_id::text =
tbl_assembly.id::text
)
JOIN tbl_item
ON ( tbl_assembly.component_id::text =
tbl_item.id::text
)
WHERE tbl_line_item.number = v_number
AND tbl_line_item.line = v_line
ORDER BY tbl_line_item.number,
tbl_line_item.line,
tbl_assembly.component_id
LOOP
RETURN NEXT r_row;
END LOOP;
DROP SEQUENCE row_number;
ELSIF v_item_type = 'THIS'
OR v_item_type = 'THAT'
OR v_item_type = 'OTHER' THEN
FOR r_row IN SELECT
[snip]
LOOP
RETURN NEXT r_row;
END LOOP;
END IF;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;

Although I have no need to limit the output I tried it just for giggles and it
worked fine.

SELECT * FROM func_bom(12345, 1) WHERE subline between 4 AND 6;

Thanks!

Kind Regards,
Keith

On Fri, 2005-05-20 at 13:27, Keith Worthington wrote:

> Scott,
>
> I realize that this thread went off in another direction however your
> suggestion proved very helpful for a problem that I was trying to solve. I
> wanted the row number of a set returned by a function. Here is a chopped
> version of the function that I wrote.
>
> CREATE OR REPLACE FUNCTION func_bom(integer, integer)
> RETURNS SETOF func_bom AS

SNIP

> Although I have no need to limit the output I tried it just for giggles and it
> worked fine.
>
> SELECT * FROM func_bom(12345, 1) WHERE subline between 4 AND 6;

You're welcome. I've saved that off to my ~/pgsql directory for future
use. So, the thanks are back to you. :)