Query fails to parse when using embed, views, and selecting multiple columns

[rafibayer]

Version

1.28.0

What happened?

Hi all, I ran into an issue trying to write a query using views and sqlc.embed. I am not sure if what I want to do is supported, but it "almost" works, and would be very useful.

What I noticed, is that if I have a table, say foos, and a view on that table, view_foos, sqlc will let me sqlc.embed(foos) a result from a query using view_foos.

This seems to work if and only if the sqlc.embed is the only thing selected in the query, as soon as I try to add another column, embed, anything else, the parser fails.

Working version:
https://play.sqlc.dev/p/028ba999022e364f9bb968af1a8cc72440093e5c47e7f6ba72ec5f2692fa4a46

-- name: TestQuery :many
SELECT sqlc.embed(foos)
FROM view_foos;

Failing version:
https://play.sqlc.dev/p/bb8cd9fff4fe52f1a1e4dce1dc576c583f24e3fbe89bf385972e1c9c0df2e5a9

-- name: TestQuery :many
SELECT sqlc.embed(foos), 1 as test
FROM view_foos;

For simplicity, I just projected a random value as column, but this repros with anything (for example, a column from a joined table).

If this worked, it would be a great way to write a query that returns the same codegen type on a view of a real table. The use-case I had was soft-deletion, I have a real table, and a view on that table that excludes soft-deleted rows. If this worked, I could write queries that return my original type instead of my soft-deleted view type (which is identical other than in name). Maybe there is another way to achieve what I want, for example an override in sqlc.yaml?

Thanks!

Relevant log output

query.sql:2:9: edited query syntax is invalid: syntax error at or near ","

Database schema

CREATE TABLE foos (
  a INT
);

CREATE VIEW view_foos AS SELECT * FROM foos;

SQL queries

-- name: TestQuery :many
-- working version
SELECT sqlc.embed(foos)
FROM view_foos;

-- name: TestQuery :many
-- broken version
SELECT sqlc.embed(foos), 1 as test
FROM view_foos;

Configuration

{
  "version": "2",
  "sql": [{
    "schema": "schema.sql",
    "queries": "query.sql",
    "engine": "postgresql",
    "gen": {
      "go": {
        "out": "db",
        "emit_result_struct_pointers": true
      }
    }
  }]
}

Playground URL

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

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[rafibayer]

If the motivation for doing something like this is unclear, check out the generated query in the working playground link:
https://play.sqlc.dev/p/028ba999022e364f9bb968af1a8cc72440093e5c47e7f6ba72ec5f2692fa4a46

My query is on view_foos, but returns a Foo generated type, which is exactly what I want!

const testQuery = `-- name: TestQuery :many
SELECT 
FROM view_foos
`

type TestQueryRow struct {
	Foo Foo
}

[narma]

@rafibayer This is working workaround

SELECT sqlc.embed(foos), 1 as test
FROM view_foos foos;

It's also required for first "working" version, otherwise sqlc silently generates invalid sql.