February 16th, 2008 by depesz | Tags: , | 2 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

i will try to follow development of 8.4, and write examples of what's possible with it, based on current HEAD code.

of course there is no guarantee that it will work in final, released 8.4 (it is a bit too early to talk about it, but given the fact that the patch got committed, there is pretty good chance we will see it whenever 8.4 will be released.

today 2 new features: limit (select ) and optional as in select.

first feature: limit (select )

this is actually not new feature, but rather removed limitation from code.

in pg (up to 8.3) this will fail:

create table x (id int4 primary key, field int4);
insert into x (id, field) select i, random() * 10000 from generate_series(1,100) i;
select * from x order by field limit (select count(*) / 10 from x);

error will be:

# select * from x order by field limit (select count(*) / 10 from x);
ERROR: argument of LIMIT must not contain subqueries

but in 8.4, you can do it:

# select * from x order by field limit (select count(*) / 10 from x);
id | field
----+-------
4 | 40
86 | 159
69 | 267
46 | 478
55 | 543
61 | 698
32 | 875
35 | 916
73 | 1072
15 | 1090
(10 rows)

it is not terribly useful, but there are cases that you'll need it.

next feature brings pgsql a bit closer to sql standard. apparently standard says that “as" is optional.

i.e., it should be possible to write:

select field some_name, other_field some_other_name from table ...

instead of:

select field as some_name, other_field as some_other_name from table ...

but, in postgresql it was not available:

# select id x_id, field random_value from x limit 1;
ERROR: syntax error at or near "x_id"
LINE 1: select id x_id, field random_value from x limit 1;
^

in 8.4 though, you can:

# select id x_id, field random_value from x limit 1;
x_id | random_value
------+--------------
1 | 6449
(1 row)

both commits by tom lane.

  1. 2 comments

  2. # Vincenzo Romano
    Feb 18, 2008

    I suppose that also the OFFSET limitations will go away.
    There’s no need and “paged” selects are quite common!

  3. # Daniel Cristian Cruz
    Feb 20, 2008

    The second feature could hide some buggy SQL without “,” between field names…

    Don’t like it…

Leave a comment