I've been send a patch to the parser that changes the FETCH statement to not
accept an empty portal name anymore and and allows FETCH without IN/FROM.

First of all I really like to add this to ECPG since the different FETCH
syntax is a major compatibility problem. But I do not like to have ECPG's
parser accept the statement while the backend does not. Since this is not a
standard feature I wonder what others think about it.

My point of view is that I'd like to have as much compatibility as possible.

Michael
-- 
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael@Fam-Meskes.De           | Use PostgreSQL!

Wed, 12 Jan 2000 16:12:16 +0100
Michael Meskes <meskes@postgresql.org>
FETCH without FROM/IN
Michael Meskes <meskes@postgreSQL.org> writes:
> I've been send a patch to the parser that changes the FETCH statement to not
> accept an empty portal name anymore and and allows FETCH without IN/FROM.
> First of all I really like to add this to ECPG since the different FETCH
> syntax is a major compatibility problem. But I do not like to have ECPG's
> parser accept the statement while the backend does not. Since this is not a
> standard feature I wonder what others think about it.

It looks to me like the backend grammar *does* accept FETCH without
IN/FROM cursor.  Which seems pretty bizarre --- I don't understand how
it makes sense to omit a cursor name from FETCH.

Looking at the SQL92 spec, it seems we are mighty far away from any
defensible reading of the spec :-(.  The spec says

         <fetch statement> ::=
              FETCH [ [ <fetch orientation> ] FROM ]
                <cursor name> INTO <fetch target list>

         <fetch orientation> ::=
                NEXT
              | PRIOR
              | FIRST
              | LAST
              | { ABSOLUTE | RELATIVE } <simple value specification>

         <fetch target list> ::=
              <target specification> [ { <comma> <target specification> }... ]

whereas gram.y has

FetchStmt:    FETCH opt_direction fetch_how_many opt_portal_name
        |    MOVE opt_direction fetch_how_many opt_portal_name
        ;

opt_direction:    FORWARD
        | BACKWARD
        | RELATIVE
        | ABSOLUTE
        | /*EMPTY*/
        ;

fetch_how_many:  Iconst
        | '-' Iconst
        | ALL
        | NEXT
        | PRIOR
        | /*EMPTY*/
        ;

opt_portal_name:  IN name
        | FROM name
        | /*EMPTY*/
        ;

Are we compatible with anything at all???

			regards, tom lane

Wed, 12 Jan 2000 11:09:11 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] FETCH without FROM/IN 
> Looking at the SQL92 spec, it seems we are mighty far away from any
> defensible reading of the spec :-(...
> Are we compatible with anything at all???

Although not rigorously compatible, it appears that we do allow
compatible syntax:

FETCH 4 FROM t1;
FETCH NEXT FROM t1;

But afaik our cursor behavior does not currently allow supporting

FETCH FIRST FROM t1; -- cursor can't be positioned to first/last
FETCH ABSOLUTE 4 FROM t1; -- not sure about this one...
FETCH RELATIVE 4 FROM t1; -- this could be a MOVE/FETCH combination?

so we, uh, don't support it (yet). 

I'd suggest definitely supporting all SQL92 syntax that the cursor can
manage, and also supporting the existing Postgres behaviors (which may
only be a simple subset). If we have just *alternate* syntax for the
same thing, then v7.0 would be a good time to straighten it up.

                     - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Wed, 12 Jan 2000 16:43:07 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] FETCH without FROM/IN
On Wed, Jan 12, 2000 at 11:09:11AM -0500, Tom Lane wrote:
> It looks to me like the backend grammar *does* accept FETCH without
> IN/FROM cursor.  Which seems pretty bizarre --- I don't understand how
> it makes sense to omit a cursor name from FETCH.

Yes, it does accept if NO portal name is given. This is corrected by the
patch. But what I wanted to talk about is the IN/FROM keyword. 

>          <fetch statement> ::=
>               FETCH [ [ <fetch orientation> ] FROM ]
>                 <cursor name> INTO <fetch target list>

To me this seems to say that FROM is just optional. Okay, if I make it
optional in our parser?

Michael
-- 
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael@Fam-Meskes.De           | Use PostgreSQL!

Wed, 12 Jan 2000 17:53:26 +0100
Michael Meskes <meskes@postgreSQL.org>
Re: [HACKERS] FETCH without FROM/IN
On Wed, Jan 12, 2000 at 04:43:07PM +0000, Thomas Lockhart wrote:
> FETCH RELATIVE 4 FROM t1; -- this could be a MOVE/FETCH combination?
> 
> so we, uh, don't support it (yet). 

How about

FETCH t1;?

Michael
-- 
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael@Fam-Meskes.De           | Use PostgreSQL!

Wed, 12 Jan 2000 17:56:37 +0100
Michael Meskes <meskes@postgreSQL.org>
Re: [HACKERS] FETCH without FROM/IN
Michael Meskes <meskes@postgreSQL.org> writes:
>          <fetch statement> ::=
>               FETCH [ [ <fetch orientation> ] FROM ]
>                 <cursor name> INTO <fetch target list>

> To me this seems to say that FROM is just optional. Okay, if I make it
> optional in our parser?

Careful --- notice that FROM is only optional if you *also* omit all the
preceding optional clauses.  Otherwise there will be a reduce conflict
that you could only resolve by removing all of FETCH's secondary
keywords from the ColId list.  I don't think that would be an acceptable
tradeoff.

I think, though, that you could make our syntax work like
	FETCH [ opt_direction fetch_how_many FROM/IN ] portal_name
without conflicts.  That'd be good since it'd be more like SQL92.

			regards, tom lane

Wed, 12 Jan 2000 20:13:02 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] FETCH without FROM/IN 
On Wed, Jan 12, 2000 at 08:13:02PM -0500, Tom Lane wrote:
> I think, though, that you could make our syntax work like
> 	FETCH [ opt_direction fetch_how_many FROM/IN ] portal_name
> without conflicts.  That'd be good since it'd be more like SQL92.

Yes. I just read the patch I got from Rene in detail and it seems to
implement:

FETCH [ <direction> [ <fetch_how_many> ]] [ FROM/IN ] portal_name;

Both direction and fetch_how_many are no longer optional in that they could
be replaced by an empty string I wonder if this is correct. It would mean
that we have to specify an amount resp. all everytime we do give a
direction.

However, I think it should be possible to make it:

FETCH [ <direction> ][ <fetch_how_many> ] [ FROM/IN ] portal_name;

This seems better, isn't it?

Michael
- 
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael@Fam-Meskes.De           | Use PostgreSQL!

Thu, 13 Jan 2000 08:48:16 +0100
Michael Meskes <meskes@postgreSQL.org>
Re: [HACKERS] FETCH without FROM/IN
Michael Meskes <meskes@postgreSQL.org> writes:
> On Wed, Jan 12, 2000 at 08:13:02PM -0500, Tom Lane wrote:
>> I think, though, that you could make our syntax work like
>> FETCH [ opt_direction fetch_how_many FROM/IN ] portal_name
>> without conflicts.  That'd be good since it'd be more like SQL92.

Note: I was assuming the same definitions of 'opt_direction' and
'fetch_how_many' as are in the current gram.y; namely, they can
expand to either an option phrase or empty.  So what I was really
saying is
    FETCH [ [ direction ] [ how_many ] FROM/IN ] portal_name

> Yes. I just read the patch I got from Rene in detail and it seems to
> implement:

> FETCH [ <direction> [ <fetch_how_many> ]] [ FROM/IN ] portal_name;

Certainly we currently accept a how_many clause without a preceding
direction, so if the patch removes that possibility then it's wrong.

> However, I think it should be possible to make it:

> FETCH [ <direction> ][ <fetch_how_many> ] [ FROM/IN ] portal_name;

> This seems better, isn't it?

If you do it like that (ie, the portal name is now required), I *think*
it will work without shift-reduce conflicts in the current grammar,
but we may regret it later when we try to do more of SQL92.  I would
recommend sticking to an SQL92-like syntax, in which FROM/IN is not
optional if direction and/or how_many appear.

The reason I'm concerned about this is that all of the direction and
howmany keywords are considered valid ColIds (and if we take them out
of the ColIds list, we risk breaking databases that work at present).
That means that the parser has some difficulty in figuring out whether
an apparent keyword is really a keyword, or a portal name that happens
to be the same as a keyword.  For example, consider

	FETCH NEXT;

If both FROM and portal_name were optional, this statement would
actually be ambiguous: is it FETCH NEXT from the default portal,
or FETCH with default options from a cursor named NEXT?

In the syntax you are proposing, this statement is valid and not
ambiguous --- NEXT must be a cursor name --- but the only way an
LR(1) parser can figure that out is to look ahead one token to see
that semicolon comes next.

What I'm concerned about is that SQL92 allows other options *after*
the cursor name, and we may someday want to support those.  We could
easily find that the grammar is no longer LR(1) (ie, it takes more than
one token lookahead to decide whether we have the portal name or not);
and then we've got trouble.  If FROM is required after FETCH options
then this risk is much reduced.

Another reason for requiring FROM/IN is that we will not be able to
expand the "FETCH n" syntax for how_many to handle a more general
expression (as opposed to a bare signed-integer constant, as we have
now) unless there is a delimiter between it and the portal name.
We still have unresolved headaches in the grammar that come from the
lack of delimiters around constant expressions in column DEFAULT
options; let's not add another source of the same kind of trouble.

In short, the syntax

    FETCH [ [ direction ] [ how_many ] FROM/IN ] portal_name

poses much less risk for future expansion than the syntax

    FETCH [ direction ] [ how_many ] [ FROM/IN ] portal_name

and that's why I think we'd be safer to stick with the former.

			regards, tom lane

Thu, 13 Jan 2000 11:34:31 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] FETCH without FROM/IN 
On Thu, Jan 13, 2000 at 11:34:31AM -0500, Tom Lane wrote:
>     FETCH [ [ direction ] [ how_many ] FROM/IN ] portal_name

Looks good to me.

> Certainly we currently accept a how_many clause without a preceding
> direction, so if the patch removes that possibility then it's wrong.

Sure. Rene already send me a fix.

> If you do it like that (ie, the portal name is now required), I *think*
> it will work without shift-reduce conflicts in the current grammar,
> but we may regret it later when we try to do more of SQL92.  I would
> recommend sticking to an SQL92-like syntax, in which FROM/IN is not
> optional if direction and/or how_many appear.
> 
> The reason I'm concerned about this is that all of the direction and
> howmany keywords are considered valid ColIds (and if we take them out
> of the ColIds list, we risk breaking databases that work at present).
> That means that the parser has some difficulty in figuring out whether
> an apparent keyword is really a keyword, or a portal name that happens
> to be the same as a keyword.  For example, consider
> 
> 	FETCH NEXT;
> 
> If both FROM and portal_name were optional, this statement would
> actually be ambiguous: is it FETCH NEXT from the default portal,

Do we have a default portal?

> or FETCH with default options from a cursor named NEXT?
> ... 
> What I'm concerned about is that SQL92 allows other options *after*
> the cursor name, and we may someday want to support those.  We could
> easily find that the grammar is no longer LR(1) (ie, it takes more than
> one token lookahead to decide whether we have the portal name or not);
> and then we've got trouble.  If FROM is required after FETCH options
> then this risk is much reduced.

Yes, I completely agree on this one.

I will try to change the syntax to what you proposed.

Michael
-- 
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael@Fam-Meskes.De           | Use PostgreSQL!

Fri, 14 Jan 2000 07:47:17 +0100
Michael Meskes <meskes@postgreSQL.org>
Re: [HACKERS] FETCH without FROM/IN
Michael Meskes <meskes@postgreSQL.org> writes:
>> FETCH NEXT;
>> 
>> If both FROM and portal_name were optional, this statement would
>> actually be ambiguous: is it FETCH NEXT from the default portal,

> Do we have a default portal?

Darn if I know, but the current gram.y thinks so.  If I try it
without any preparation, I get:

regression=# fetch;
NOTICE:  PerformPortalFetch: blank portal unsupported
FETCH

but maybe with some magic DECLARE beforehand, it'd work?
Anyone know?

Since the SQL92 spec clearly requires a cursor name to be provided,
I'd be willing to see us remove the option of defaulting the cursor
name.  Is there anyone out there who knows what it does and wants
to argue we should keep it?

			regards, tom lane

Fri, 14 Jan 2000 02:38:04 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] FETCH without FROM/IN 