Skip to content

Postgres @named parameters don't always work #605

Closed as not planned
Closed as not planned
Postgres @named parameters don't always work#605
Labels
📚 postgresqlbugSomething isn't working

Description

@tv42
tv42
opened on Jul 21, 2020

Based on https://github.com/kyleconroy/sqlc/blob/master/docs/named_parameters.md
@foo and sqlc.arg(foo) are identical. However:

-- schema says create table foo ( id bigint );
-- name: demo1 :exec
DELETE FROM foo WHERE id=$1;
-- name: demo2 :exec
DELETE FROM foo WHERE id=@id;
-- name: demo3 :exec
DELETE FROM foo WHERE id=sqlc.arg(id);

results in

$ grep ^func demo.sql.go
func (q *Queries) demo1(ctx context.Context, id sql.NullInt64) error {
func (q *Queries) demo2(ctx context.Context) error {
func (q *Queries) demo3(ctx context.Context, id sql.NullInt64) error {

The @foo form doesn't get recognized in this scenario.

Activity

  1. kyleconroy
    added
    bugSomething isn't working
    📚 postgresql
    on Jul 21, 2020

  2. kyleconroy commented on Jul 21, 2020

    @kyleconroy
    kyleconroy
    on Jul 21, 2020
    Collaborator

    I'm not sure why, but the @ form works when there's a space between the = and @. Take a look here: https://play.sqlc.dev/p/a2beb1f05746a8cac18a0d161645866c83f95896311b1e1b8ad5943199d7c5dd

    Long term, I think I'm going to regret adding the @ form of named parameters because of issues like this.

  3. maxhawkins commented on Jul 26, 2020

    @maxhawkins
    maxhawkins
    on Jul 26, 2020
    Contributor

    Sorry if this is digging up something that got settled in #71, but I wish the syntax was :param instead of @param. That way you could use psql to debug your queries against a running database:

    psql --variable "param=1" -f queries.sql database

    Unsure if using that syntax would help with the parsing problem here.

  4. kyleconroy commented on Aug 28, 2021

    @kyleconroy
    kyleconroy
    on Aug 28, 2021
    Collaborator

    @maxhawkins The :param syntax doesn't work with the MySQL parser.

  5. kyleconroy
    closed this as not plannedon Sep 22, 2023

  6. tv42 commented on May 29, 2024

    @tv42
    tv42
    on May 29, 2024
    Author

    If @foo is not even supposed to work right, https://docs.sqlc.dev/en/latest/howto/named_parameters.html needs to be updated to not recommend it as a shortcut.

    If the sqlc.arg() syntax is too verbose for your taste, you can use the @ operator as a shortcut.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    📚 postgresqlbugSomething isn't working

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions