Named parameters

[Virgiel]

In one of my projects, I have long queries like this one:

INSERT INTO pro 
  (title, fname, lname, sex, photo, specialty, expertise, street, postcode, city, phone, link, lat, lng, source, show, trait)
  VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17) 
  RETURNING id;

Having to repeat all the names is cumbersome, even more, when I need to do it for all inserts and updates. I found your previous Reddit comment on that matter. I understand the appeal of using valid SQL, but i think we should also support named parameters.

We could use MySQL syntax @name that should be highlighted by code editor, an extended Postgres one $name or another syntax found in SQLDelight :name. Indexed and named parameters should be incompatible, so you can either write

--! insert_pro(title, fname, ..., trait)
INSERT INTO pro 
  (title, fname, ..., trait)
  VALUES ($1, $2, ..., $17) 
  RETURNING id;

or

--! insert_pro()
INSERT INTO pro 
  (title, fname, ..., trait)
  VALUES (:title, :fname, ..., :trait) 
  RETURNING id;

[LouisGariepy]

I like the "opt-in" approach you're suggesting. Users that want the "extended" naming syntax can opt into it (and break postgres compatibility). I'd probably remove the parenthesis altogether in the "extended syntax" case (e.g. --! insert_pro).

One thing I'm worried about though is false positives. It's conceivable that a user would have the tokens $some_identifier inside say a string literal, and that would throw the parser off completely. I'll probably have to look for some prior art from similar libraries, but on the face of it I'm willing to go forward with this proposal.

[LouisGariepy]

I settled on the colon :, because IDEs totally freak-out when using other tokens like @, $ and ?.

[Virgiel]

Is there a public branch or fork where I can see your current changes?

[LouisGariepy]

@Virgiel I have some time to work on Cornucopia today. I'll make the branch public probably today, or at latest tomorrow 😄. The only missing part is codegen now. Things are coming along nicely.

[LouisGariepy]

@Virgiel Hey! I just published my working branch for v0.8 #52 !

I used more time on refactors than anticipated, and also coordinated to launch a website for the project, so I didn't have as much time as I wanted to put on code generation. I'm going to keep working on it today :)

[Virgiel]

@LouisGariepy Looks promising! Tell me if you need any help with code generation