René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Insert statement with `WITH` clause used as `SELECT` field","articleBody":"### Version\r\n\r\n1.22.0\r\n\r\n### What happened?\r\n\r\nThis is similar to #2350.\r\n\r\nI've tried 2 variations of the same query using `WITH (v AS ...)` and referencing that `v` as one of the `SELECT`ed fields. While both variations produce the expected results using `psql`, they fail with different errors when compiling them using `sqlc`.\r\n\r\n1. Using `INSERT INTO ... WITH ... SELECT ...\r\n  \r\n  ```sql\r\n  -- name: GenerateNewHistoricalData :exec\r\n  INSERT INTO historical_data(id, default_value, version)\r\n    WITH v AS (SELECT nextval('historical_data_version_seq'))\r\n  SELECT id, default_value, (table v)\r\n  FROM data;\r\n  ```\r\n  **Error**: `query.sql:18:1: relation \"v\" does not exist`\r\n\r\n2. Using `WITH ... INSERT INTO ... SELECT ...`:\r\n\r\n  ```sql\r\n  -- name: GenerateNewHistoricalDataTakeTwo :exec\r\n  WITH v AS (SELECT nextval('historical_data_version_seq'))\r\n  INSERT INTO historical_data(id, default_value, version)\r\n  SELECT id, default_value, (table v)\r\n  FROM data;\r\n  ```\r\n  **Error**: `query.sql:24:1: edit start location is out of bounds`\r\n\r\n\r\n### Relevant log output\r\n\r\n_No response_\r\n\r\n### Database schema\r\n\r\n```sql\r\nCREATE TABLE data (\r\n  id   TEXT NOT NULL,\r\n  value TEXT NOT NULL,\r\n  CONSTRAINT data_pkey PRIMARY KEY(id)\r\n);\r\n\r\nCREATE TABLE historical_data (\r\n  id TEXT NOT NULL,\r\n  value TEXT NOT NULL,\r\n  version BIGINT NOT NULL,\r\n  CONSTRAINT historical_data_pkey PRIMARY KEY(id, version)\r\n);\r\n\r\nCREATE SEQUENCE historical_data_version_seq;\r\n```\r\n\r\n### SQL queries\r\n\r\n_No response_\r\n\r\n### Configuration\r\n\r\n_No response_\r\n\r\n### Playground URL\r\n\r\nhttps://play.sqlc.dev/p/4c877cf26c2bb27c217303995af1afbef3bf9c84a25e682f01de5f0745c259cc\r\n\r\n### What operating system are you using?\r\n\r\nmacOS\r\n\r\n### What database engines are you using?\r\n\r\nPostgreSQL\r\n\r\n### What type of code are you generating?\r\n\r\nGo","author":{"url":"https://github.com/kampde","@type":"Person","name":"kampde"},"datePublished":"2023-10-19T12:00:04.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":2},"url":"https://github.com/2886/sqlc/issues/2886"}