RETURNING syntax

[shutej]

it'd be really nice to be able to automatically produce syntax RETURNING id. i can hack around it with other methods for now.

[0x6e6562]

This seems like a good idea. The thing I'm thinking about is how to keep it portable, since the syntax across MySQL and Postgres differs on this point. What DB are you using?

[shutej]

Postgres.MySQL doesn't have this problem I think. They use the stock LastInsertID or some such... Jeremy From: Ben HoodSent: Saturday, December 20, 2014 5:08 PMTo: relops/sqlcReply To: relops/sqlcCc: Jeremy ShuteSubject: Re: [sqlc] RETURNING syntax (#5)This seems like a good idea. The thing I'm thinking about is how to keep it portable, since the syntax across MySQL and Postgres differs on this point. What DB are you using?

—Reply to this email directly or view it on GitHub.

[0x6e6562]

Of course the next issue that you run into is that Postgres will allow a lot more than just the id in a RETURNING clause.

[shutej]

Yeah I don't actually think this is that simple. When you have a fluent
interface builder and the fluent interface reflects the EBNF of the grammar
that's accepted by a variety of implementations, you're going to end up
with some runtime issues unless you have separate type-safe implementations
per dialect.

I don't think RETURNING is valid in MySQL for instance.

I'm not sure what the "right" thing to do in this case is... Perhaps a
PEG-to-fluent-interface builder or EBNF-to-fluent-interface builder would
allow libraries to simply focus on what language could be generated in the
dialect. (The inspiration here is camlp4 which has ways of quasi-quoting
portions of syntax trees, sort of like LISP macros.)

Jeremy

On Sat, Dec 20, 2014 at 6:13 PM, Ben Hood notifications@github.com wrote:

Of course the next issue that you run into is that Postgres will allow a
lot more than just the id in a RETURNING clause.

—
Reply to this email directly or view it on GitHub
#5 (comment).

[lukaseder]

This seems like a good idea. The thing I'm thinking about is how to keep it portable, since the syntax across MySQL and Postgres differs on this point.

To my knowledge, only PostgreSQL and Firebird have this syntax. Oracle has it, but only in PL/SQL, not in SQL, and DB2 can emulate it like so:

SELECT id FROM FINAL TABLE(INSERT INTO t VALUES(...))

SQL Server knows the OUTPUT clause, which is similar but not exactly the same. Some dialects support returning values via their respective wire protocol (which is exposed in JDBC's Statement.getGeneratedKeys(), for instance), though I don't know if this is available easily in Go. Also, some wire protocols / databases allow for returning only identity values, others allow for returning all sorts of values (including trigger-generated ones, in particular Oracle and HSQLDB)

Then, of course, there is also the possibility of multi-row-insert:

INSERT INTO t VALUES (1, 'a'), (2, 'b'), (3, 'c') RETURNING id;
INSERT INTO t SELECT a, b FROM t2 RETURNING id;

And later on, the same can be done with UPDATE and DELETE. In any case, getting this right in all databases isn't easy... :)

[0x6e6562]

@lukaseder As I was writing my last comment, I was thinking, what would JOOQ do, and then speak of the devil :-)

So many thanks for sharing your vast experience with us.

So what is the idiomatic way of doing this with JOOQ - is there something in the fluent API or do you defer to the driver?

[0x6e6562]

I've begun some work on this on the returning branch in order to sketch out how this might look, but it's not functional yet.

[0x6e6562]

I've merged the simplest solution possible that supports Postgres, there are a number of improvements that could be made, but it would be good to get some feedback to see this approach helps at all.

[lukaseder]

So what is the idiomatic way of doing this with JOOQ - is there something in the fluent API or do you defer to the driver?

The fluent API contains a RETURNING clause:
http://www.jooq.org/javadoc/latest/org/jooq/InsertReturningStep.html

The implementation, however, is driver-specific, indeed. The Open Source implementations can be seen here:
https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/AbstractStoreQuery.java

grep for returning

[0x6e6562]

Many thanks for the heads up - having a reference implementation is a very good thing :-)

[lukaseder]

Woohoo. jOOQ is a reference implementation ;-)

Just ping me if you have any questions!

[0x6e6562]

Closing this issue seeing as it has long since been implemented.