test: Add case for #2187

sqlc-dev · kyleconroy · Oct 18, 2023 · Oct 18, 2023 · Oct 18, 2023 · Oct 18, 2023
commit cbc15c46efc56f5ed7cb8e8f97fbcd2f0f1f8ff5
diff --git a/internal/endtoend/testdata/cte_recursive_union/issue.md b/internal/endtoend/testdata/cte_recursive_union/issue.md
@@ -0,0 +1 @@
+# TODO
diff --git a/internal/endtoend/testdata/cte_recursive_union/postgresql/pgx/exec.json b/internal/endtoend/testdata/cte_recursive_union/postgresql/pgx/exec.json
@@ -0,0 +1,3 @@
+{
+  "contexts": ["managed-db"]
+}
diff --git a/internal/endtoend/testdata/cte_recursive_union/postgresql/pgx/go/db.go b/internal/endtoend/testdata/cte_recursive_union/postgresql/pgx/go/db.go
diff --git a/internal/endtoend/testdata/cte_recursive_union/postgresql/pgx/go/models.go b/internal/endtoend/testdata/cte_recursive_union/postgresql/pgx/go/models.go
diff --git a/internal/endtoend/testdata/cte_recursive_union/postgresql/pgx/go/query.sql.go b/internal/endtoend/testdata/cte_recursive_union/postgresql/pgx/go/query.sql.go
diff --git a/internal/endtoend/testdata/cte_recursive_union/postgresql/pgx/query.sql b/internal/endtoend/testdata/cte_recursive_union/postgresql/pgx/query.sql
@@ -0,0 +1,18 @@
+-- name: ListCaseIntentHistory :many
+WITH RECURSIVE descendants AS
+   ( SELECT case_intent_parent_id AS parent, case_intent_id AS child, 1 AS lvl
+     FROM case_intent_parent_join
+     UNION ALL
+     SELECT d.parent as parent, p.case_intent_id as child, d.lvl + 1 as lvl
+     FROM descendants d
+              JOIN case_intent_parent_join p
+                   ON d.child = p.case_intent_parent_id
+   )
+select distinct child, 'child' group_
+from descendants
+where parent = @case_intent_id
+union
+select distinct parent, 'parent' group_
+from descendants
+where child = @case_intent_id
+ORDER BY child;
diff --git a/internal/endtoend/testdata/cte_recursive_union/postgresql/pgx/schema.sql b/internal/endtoend/testdata/cte_recursive_union/postgresql/pgx/schema.sql
@@ -0,0 +1,27 @@
+CREATE TABLE case_intent_version
+(
+    version_id SERIAL NOT NULL PRIMARY KEY,
+    reviewer TEXT NOT NULL,
+    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
+);
+CREATE TABLE case_intent
+(
+    id SERIAL NOT NULL PRIMARY KEY,
+    case_intent_string TEXT NOT NULL,
+    description TEXT NOT NULL,
+    author TEXT NOT NULL
+);
+CREATE TABLE case_intent_parent_join
+(
+    case_intent_id BIGINT NOT NULL,
+    case_intent_parent_id BIGINT NOT NULL,
+    constraint fk_case_intent_id foreign key (case_intent_id) references case_intent(id),
+    constraint fk_case_intent_parent_id foreign key (case_intent_parent_id) references case_intent(id)
+);
+CREATE TABLE case_intent_version_join
+(
+    case_intent_id BIGINT NOT NULL,
+    case_intent_version_id INT NOT NULL,
+    constraint fk_case_intent_id foreign key (case_intent_id) references case_intent(id),
+    constraint fk_case_intent_version_id foreign key (case_intent_version_id) references case_intent_version(version_id)
+);
diff --git a/internal/endtoend/testdata/cte_recursive_union/postgresql/pgx/sqlc.yaml b/internal/endtoend/testdata/cte_recursive_union/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"