test: Add case for #2731

sqlc-dev · kyleconroy · Oct 18, 2023 · Oct 18, 2023 · Oct 18, 2023 · Oct 18, 2023
commit 61b86e90f47953c2ca18764426e46dbb01b262d7
diff --git a/internal/endtoend/testdata/json_array_elements/issue.md b/internal/endtoend/testdata/json_array_elements/issue.md
@@ -0,0 +1 @@
+https://github.com/sqlc-dev/sqlc/issues/2731
diff --git a/internal/endtoend/testdata/json_array_elements/postgresql/pgx/exec.json b/internal/endtoend/testdata/json_array_elements/postgresql/pgx/exec.json
@@ -0,0 +1,3 @@
+{
+  "contexts": ["managed-db"]
+}
diff --git a/internal/endtoend/testdata/json_array_elements/postgresql/pgx/go/db.go b/internal/endtoend/testdata/json_array_elements/postgresql/pgx/go/db.go
diff --git a/internal/endtoend/testdata/json_array_elements/postgresql/pgx/go/models.go b/internal/endtoend/testdata/json_array_elements/postgresql/pgx/go/models.go
diff --git a/internal/endtoend/testdata/json_array_elements/postgresql/pgx/go/query.sql.go b/internal/endtoend/testdata/json_array_elements/postgresql/pgx/go/query.sql.go
diff --git a/internal/endtoend/testdata/json_array_elements/postgresql/pgx/query.sql b/internal/endtoend/testdata/json_array_elements/postgresql/pgx/query.sql
@@ -0,0 +1,6 @@
+-- name: GetActionCodeByResource :one
+SELECT code, arr.item_object ->> 'code'  as resource_code
+FROM sys_actions,
+    jsonb_array_elements(resources) with ordinality arr(item_object, resource)
+    WHERE item_object->>'resource' = sqlc.arg('resource')
+    LIMIT 1;
diff --git a/internal/endtoend/testdata/json_array_elements/postgresql/pgx/schema.sql b/internal/endtoend/testdata/json_array_elements/postgresql/pgx/schema.sql
@@ -0,0 +1,8 @@
+CREATE TABLE "sys_actions" (
+    "id" int8 NOT NULL,
+    "code" varchar(20) NOT NULL,
+    "menu" varchar(255) NOT NULL,
+    "title" varchar(20) NOT NULL,
+    "resources" jsonb,
+    PRIMARY KEY ("id")
+);
diff --git a/internal/endtoend/testdata/json_array_elements/postgresql/pgx/sqlc.yaml b/internal/endtoend/testdata/json_array_elements/postgresql/pgx/sqlc.yaml
@@ -0,0 +1,10 @@
+version: "2"
+sql:
+  - engine: "postgresql"
+    schema: "schema.sql"
+    queries: "query.sql"
+    gen:
+      go:
+        package: "querytest"
+        out: "go"
+        sql_package: "pgx/v5"