Re: SEARCH and CYCLE clauses

On 2020-05-22 12:40, Vik Fearing wrote:
>> If you specify null, then the cycle check expression will always fail,
>> so it will abort after the first row.  (We should perhaps prohibit
>> specifying null, but see below.)
>
> I would rather make the cycle check expression work with null.

It works correctly AFAICT. What is your expectation?

>> This is something we need to think about.  If we want to check at parse
>> time whether the two values are not the same (and perhaps not null),
>> then we either need to restrict the generality of what we can specify,
>> or we need to be prepared to do full expression evaluation in the
>> parser.  A simple and practical way might be to only allow string and
>> boolean literal.  I don't have a strong opinion here.
>
>
> I'm with Pavel on this one. Why does it have to be a parse-time error?
> Also, I regularly see people write functions as sort of pauper's
> variables, but a function call isn't allowed here.

If not parse-time error, at what time do you want to check it?

> As an improvement over the spec, I think the vast majority of people
> will be using simple true/false values. Can we make that optional?
>
> CYCLE f, t SET is_cycle USING path
>
> would be the same as
>
> CYCLE f, t SET is_cycle TO true DEFAULT false USING path

I was also considering that. It would be an easy change to make.

(Apparently, in DB2 you can omit the USING path clause. Not sure how to
make that work, however.)

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services