sqlc-dev / sqlc

Generate type-safe code from SQL
https://sqlc.dev
MIT License
13.58k stars 811 forks source link

`DELETE USING RETURNING` syntax error at or near "," #3188

Open ghost opened 9 months ago

ghost commented 9 months ago

Version

1.25.0

What happened?

Fails to generate - syntax error at or near ",". This appears to be due to having multiple items in the RETURNING clause given that if I change it to only have a single item it generates as expected. This bug does not appear to happen if the items are single columns, only if they are either table.* or sqlc.embed(table).

Relevant log output

# package abstractions
sql/queries/connections.sql:126:0: syntax error at or near ","
gen.go:4: running "sqlc": exit status 1

Database schema

CREATE SCHEMA IF NOT EXISTS common;
CREATE TABLE IF NOT EXISTS common.data_provider_credential
(
    id                 BIGINT PRIMARY KEY,
    provider           INTEGER NOT NULL,
    credentials_object TEXT    NOT NULL,
    terra_owned        BOOLEAN NOT NULL DEFAULT FALSE,
    encryption_key     TEXT    NOT NULL,
    UNIQUE (provider, credentials_object)
);

CREATE TABLE IF NOT EXISTS common.data_provider
(
    id                   BIGINT PRIMARY KEY,
    provider             INTEGER NOT NULL,
    active               BOOLEAN NOT NULL DEFAULT TRUE,
    auth_redirect_url    TEXT    NOT NULL DEFAULT '',
    active_credential_id BIGINT           DEFAULT NULL REFERENCES common.data_provider_credential (id) ON DELETE SET NULL
);

CREATE SCHEMA IF NOT EXISTS backend;
CREATE TABLE IF NOT EXISTS backend.connection_credential
(
    id                     BIGINT PRIMARY KEY,
    provider_user_id       TEXT       NOT NULL,
    credentials_object     TEXT       NOT NULL,
    scope                  TEXT ARRAY NOT NULL,
    provider_credential_id BIGINT     NOT NULL REFERENCES common.data_provider_credential (id) ON DELETE CASCADE,
    UNIQUE (provider_user_id, provider_credential_id)
);

CREATE TABLE IF NOT EXISTS backend.connection
(
    id              BIGINT PRIMARY KEY,
    reference_id    TEXT        NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_polled_at  TIMESTAMPTZ          DEFAULT NULL,
    last_updated_at TIMESTAMPTZ          DEFAULT NULL,
    provider_id     BIGINT      NOT NULL REFERENCES common.data_provider (id) ON DELETE CASCADE,
    credential_id   BIGINT      NOT NULL REFERENCES backend.connection_credential (id) ON DELETE CASCADE,
    UNIQUE (reference_id, provider_id)
);

SQL queries

-- name: DeleteConnectionReturningConnectionDetails :one
DELETE FROM connection
USING
    connection_credential,
    data_provider,
    data_provider_credential
WHERE
    connection.credential_id = connection_credential.id
    AND connection.provider_id = data_provider.id
    AND connection_credential.provider_credential_id = data_provider_credential.id
    AND connection.id = $1
RETURNING sqlc.embed(connection), sqlc.embed(connection_credential), sqlc.embed(data_provider), sqlc.embed(data_provider_credential);

Configuration

version: "2"
sql:
  - engine: "postgresql"
    queries: ["sql/queries/common.sql", "sql/queries/connections.sql", "sql/queries/dashboard.sql"]
    schema: "sql/schemas/v0_initial_schema.sql"
    database:
      uri: "postgres://postgres:postgres@localhost:5432/postgres"
    gen:
      go:
        sql_package: "pgx/v5"
        package: "abstractions"
        out: "abstractions"

Playground URL

https://play.sqlc.dev/p/53be01f81c9ba59bf631ce1da2d05e22bdb517f2801da42eed3b9f5e902cb52a

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

kyleconroy commented 8 months ago

This is most certainly an issue with sqlc.embed in a RETURNING clause.

Abdullah-AlAttar commented 5 months ago

Having the same issue. did you find out any workaround?

ghost commented 5 months ago

Having the same issue. did you find out any workaround?

I ended up just explicitly specifying every column in the returning clause - need to wait for it to be fixed for anything better I think.