Re: sql2008 diff sql2003

From: Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: sql2008 diff sql2003
Date: 2008-09-08 17:22:27
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

>>>>> "Alvaro" == Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:

Alvaro> Wow, this is really horrid:

Alvaro> # F856 through F859: FETCH FIRST clause in subqueries,
Alvaro> views, and query expressions. The SQL:2008 syntax for
Alvaro> restricting the rows of a result set is FETCH FIRST, rather
Alvaro> than Microsoft SQL Server’s SELECT TOP N equivalent which
Alvaro> SQL Anywhere supports presently.

Alvaro> This means we have to support stuff like

Alvaro> declare foo cursor for select * from lists;
Alvaro> select * from (fetch first from foo) as bar;

No, that's wrong.

The new syntax is:

<query expression> ::=
[ <with clause> ] <query expression body>
[ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]

<result offset clause> ::=
OFFSET <offset row count> { ROW | ROWS }

<fetch first clause> ::=
FETCH { FIRST | NEXT } [ <fetch first row count> ] { ROW | ROWS } ONLY

so it's like this:

select * from foo order by bar offset 5 rows fetch first 10 rows only;

(nothing that I can see assigns any semantics to FIRST vs NEXT, they seem
to do the same thing)

Andrew (irc:RhodiumToad)

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2008-09-08 17:34:01 Re: [HACKERS] Infrastructure changes for recovery
Previous Message Andrew Gierth 2008-09-08 17:08:55 Re: Common Table Expressions (WITH RECURSIVE) patch