Insert statement with `WITH` clause used as `SELECT` field

[kampde]

Version

1.22.0

What happened?

This is similar to #2350.

I've tried 2 variations of the same query using WITH (v AS ...) and referencing that v as one of the SELECTed fields. While both variations produce the expected results using psql, they fail with different errors when compiling them using sqlc.

1. Using `INSERT INTO ... WITH ... SELECT ...

-- name: GenerateNewHistoricalData :exec
INSERT INTO historical_data(id, default_value, version)
  WITH v AS (SELECT nextval('historical_data_version_seq'))
SELECT id, default_value, (table v)
FROM data;

Error: query.sql:18:1: relation "v" does not exist

2. Using WITH ... INSERT INTO ... SELECT ...:

-- name: GenerateNewHistoricalDataTakeTwo :exec
WITH v AS (SELECT nextval('historical_data_version_seq'))
INSERT INTO historical_data(id, default_value, version)
SELECT id, default_value, (table v)
FROM data;

Error: query.sql:24:1: edit start location is out of bounds

Relevant log output

No response

Database schema

CREATE TABLE data (
  id   TEXT NOT NULL,
  value TEXT NOT NULL,
  CONSTRAINT data_pkey PRIMARY KEY(id)
);

CREATE TABLE historical_data (
  id TEXT NOT NULL,
  value TEXT NOT NULL,
  version BIGINT NOT NULL,
  CONSTRAINT historical_data_pkey PRIMARY KEY(id, version)
);

CREATE SEQUENCE historical_data_version_seq;

SQL queries

No response

Configuration

No response

Playground URL

https://play.sqlc.dev/p/4c877cf26c2bb27c217303995af1afbef3bf9c84a25e682f01de5f0745c259cc

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[kyleconroy]

The first error will be fixed by #2805. You can fix the second error by explicitly selecting the column in the subselect.

-- name: GenerateNewHistoricalDataTakeTwo :exec
WITH v AS (SELECT nextval('historical_data_version_seq') as nv)
INSERT INTO historical_data(id, value, version)
SELECT id, value, (select nv from v)
FROM data;

https://play.sqlc.dev/p/af2fda7ab8fcff8040cf19be8dd85ce78fec9a51fd4fdbab187ffa6b682939b1

[kampde]

Thanks, that works wonderfully :D

If you prefer you can close the issue now or maybe you want to keep it opened for tracking or whatever.