How to pass a named argument to function call?

[dvv]

Hello!

I'm trying to delegate to a stored procedure with optional arguments.

The following shuts down the erlang machine in a couple of seconds during the module compilation:

-- file: queries.sql
-- name: test_named_arg
-- docs: test_named_arg
select api.foo(arg := :_arg)

I believe it's due to := is considered as "a parameter named :". I believe this should be somehow guarded.

Then I tried another way:

-- file: queries.sql
-- name: test_named_arg
-- docs: test_named_arg
select api.foo(arg => :_arg)

This compiles well, but:

> Queries.test_named_arg!([_arg: "111"], run: false)
%AyeSQL.Query{arguments: ["111"], statement: "select api.foo(arg => $1)"} 

> Queries.test_named_arg!([], run: false)           
%AyeSQL.Query{arguments: [], statement: "select api.foo(arg => )"} # <------ NOTICE the lack of argument value

Please consider fixing.

Thank you for an excellent piece of software!

[alexdesousa]

Hey!

Thanks for reporting this, @dvv! I'll try to find a workaround for adding := in the future.

AyeSQL's parser is very basic and that's by design. So, AyeSQL doesn't have any knowledge of SQL. Optional arguments are mainly useful for optional query fragments and not optional arguments. Let me explain with your example:

-- file: queries.sql

-- name: test_named_arg
-- docs: test_named_arg
select api.foo(:_foo_args)

-- Omitting docs to force `@doc false`
-- name: foo_args
arg => :arg

And then your query would look something like:

iex> Queries.test_named_arg!([_foo_args: :foo_args, arg: "111"], run: false)
%AyeSQL.Query{arguments: ["111"], statement: "select api.foo(arg => $1)"} 

iex> Queries.test_named_arg!([], run: false)
%AyeSQL.Query{arguments: [], statement: "select api.foo()"}

This optional fragment is a hack I wrote almost 3 years ago, so, certainly I could improve the developer experience to make it clear how to use it.

To give you a bit of more context, I've used it to generalize ORDER BYs e.g:

-- docs: Gets users ordered by a criteria.
-- name: get_users
SELECT
  :returning_columns
FROM
  users
:_order_by

---------------------------
-- Hidden fragments

-- name: returning_columns
id,
email,
name,
(...)

-- name: order_by_email
ORDER BY email :order_direction

-- name: order_by_name
ORDER BY name :order_direction

-- name: ascending
ASC

-- name: descending
DESC

And then we would call the function get_users/1 like this:

iex> Queries.get_users(_order_by: :order_by_name, order_direction: :descending)
# Would generate: SELECT id, email, name, ... FROM users ORDER BY name DESC
iex> Queries.get_users()
# Would generate: SELECT id, email, name, ... FROM users

Note: all functions except the one with -- docs at the top will be hidden.

Two actionable things I get from this issue:

• [x] Add support for := in the parser.
• [x] Replace Optional Parameters with Optional Fragments to avoid confusion.
• [x] Improve optional fragments examples shown here.

I hope this helps :)

[dvv]

Thank you, it's much clearer now!