Support cursors (DECLARE and FETCH)

[brentd]

What do you want to change?

DECLARE x CURSOR ... and FETCH n FROM x are parsed into the ast (I believe) but not currently generated into queries.

See playground: https://play.sqlc.dev/p/c4462bcdfb5324cbc4fb45b11f0f758d8989d0fdb5888e835d7fcb80810f0170

What database engines need to be changed?

PostgreSQL

What programming language backends need to be changed?

Go

[brentd]

Current workaround available is to use LIMIT and OFFSET; the goto for stateless interactions like HTTP requests.

In my use case, I'm writing a CLI command that needs to iterate millions of rows to produce a snapshot in a particular format (protobuf). A cursor would be ideal.

With LIMIT/OFFSET, I believe it's still possible to get the consistent results a cursor provides via the REPEATABLE READ isolation level.

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT * FROM table LIMIT 1000 OFFSET ...;

COMMIT;

The downside of LIMIT+OFFSET is that the DB has to page through the entire result set behind the OFFSET each subsequent query.

[kyleconroy]

@brentd Thanks for the feature request. While I've marked this as accepted, it's currently low on the priority list of features. Hopefully someone can come along and work on it.

[dcepelik]

Hi @kyleconroy, I would like to contribute this feature. It seems to be quite straightforward, but I have a few questions:

• It seems to me that all I need to do is extend outputColumns and sourceTables to support *ast.DeclareCursorStmt and *ast.FetchStmt. In fact, if I return nil, nil from them, the generator will output functions for the queries outlined by @brentd in https://play.sqlc.dev/p/c4462bcdfb5324cbc4fb45b11f0f758d8989d0fdb5888e835d7fcb80810f0170. Obviously, that code is not correct, since it won't scan the fields in the generated FetchFromCursor function. (The DeclareCursor is correct, since it's an :exec, and doesn't return any fields.)
• The only problem is that sourceTables and outputColumns of FetchFromCursor are the ones listed in the SelectStmt of the DeclareCursor query. What would be the best way to access the DeclareCursor AST representation from FetchFromCursor? I considered storing the information in the QueryCatalog, but I'm not really sure that's conceptually correct, plus there's the obvious ordering issue. (The DeclareCursor query would have to be processed before the FetchFromCursor query. Is the order of processing the same as the order of queries in the file? And even then, I believe currently the order of queries is irrelevant, and that seems to be a desirable property.)

Thanks for any hints, and thanks for sqlc!