Generating FOR UPDATE OF query with postgres schemas fails

Version

Tested with sqlc v1.25, v1.26, v1.27

What happened?

When using sqlc with postgres the FOR UPDATE OF statement cannot be used in combination with schemas.

In postgres, this is a valid query:

SELECT *
FROM myschema.mytable
FOR UPDATE OF mytable;

However, sqlc complains that relation "mytable" does not exist.

There are two workarounds. The first is to let sqlc generate invalid SQL, and fix it afterwards.

SELECT *
FROM myschema.mytable
FOR UPDATE OF myschema.mytable;

This query will let sqlc generate Go code. But when running the code, preparing the query will fail because postgres doesn't accept FOR UPDATE OF myschema.mytable. So you'll need to run sed 's/FOR UPDATE OF myschema.mytable/FOR UPDATE OF mytable/g' path/to/queries.sql.go to fix the generate go code. ((This workaround could actually be considered to abuse a secondary bug; as it should not be possible to generate FOR UPDATE OF schema.table as a postgres query.))

The second workaround is to explicitly alias the table in the query;

SELECT *
FROM myschema.mytable AS mytable
FOR UPDATE OF mytable;

Playground URL

https://play.sqlc.dev/p/6dedaab03491761e210fc65f3be208c3e4a60c8de48052495a2dacca08a54b4b

sqlc-dev / sqlc