From ddce56630b8274d581645c16edcbf1d3b11a8e2f Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Sat, 21 Jun 2025 22:40:23 +0800
Subject: [PATCH v1 1/1] disallow user defined type for virtual generated
 column

If generated column expressions do not allow user-defined types or functions, it
makes sense to also disallow virtual generated columns from using user-defined
types.
Attached patch change CheckAttributeType to do the job.
related tests also added.

Note: Support for composite types in virtual generated columns is currently partial.
for example:

CREATE TYPE double_int as (a int, b int);
--ok
CREATE TABLE gtest4 (
    a int,
    b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) VIRTUAL
);

--not ok.
CREATE TABLE gtest4 (
  a int,
  b double_int GENERATED ALWAYS AS ((a * 2, a * 3)::double_int) VIRTUAL
);
---
 src/backend/catalog/heap.c                      | 8 ++++++++
 src/test/regress/expected/create_type.out       | 9 +++++++++
 src/test/regress/expected/generated_virtual.out | 9 +++++++++
 src/test/regress/sql/create_type.sql            | 4 ++++
 src/test/regress/sql/generated_virtual.sql      | 5 +++++
 5 files changed, 35 insertions(+)

diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 906282aae63..032ffb488ff 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -663,6 +663,14 @@ CheckAttributeType(const char *attname,
 						   containing_rowtypes,
 						   flags);
 	}
+	else if (att_typtype == TYPTYPE_BASE)
+	{
+		if ((flags & CHKATYPE_IS_VIRTUAL) && atttypid >= FirstUnpinnedObjectId)
+			ereport(ERROR,
+					errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					errmsg("virtual generated column \"%s\" cannot have a user-defined type", attname),
+					errdetail("Virtual generated columns that make use of user-defined types are not yet supported."));
+	}
 
 	/*
 	 * This might not be strictly invalid per SQL standard, but it is pretty
diff --git a/src/test/regress/expected/create_type.out b/src/test/regress/expected/create_type.out
index 5181c4290b4..9e5ee3f3c94 100644
--- a/src/test/regress/expected/create_type.out
+++ b/src/test/regress/expected/create_type.out
@@ -108,6 +108,15 @@ CREATE TYPE text_w_default (
    alignment = int4,
    default = 'zippo'
 );
+--can not use user defined type for virtual generated column
+CREATE TABLE gtest1 (a int42 GENERATED ALWAYS AS ('1') VIRTUAL);
+ERROR:  virtual generated column "a" cannot have a user-defined type
+DETAIL:  Virtual generated columns that make use of user-defined types are not yet supported.
+CREATE TABLE gtest1 (a int GENERATED ALWAYS AS ('1'::int42) VIRTUAL);
+ERROR:  generation expression uses user-defined type
+LINE 1: CREATE TABLE gtest1 (a int GENERATED ALWAYS AS ('1'::int42) ...
+                                                        ^
+DETAIL:  Virtual generated columns that make use of user-defined types are not yet supported.
 CREATE TABLE default_test (f1 text_w_default, f2 int42);
 INSERT INTO default_test DEFAULT VALUES;
 SELECT * FROM default_test;
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index 0a8c9b3abcb..1b69d43c022 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -562,6 +562,15 @@ SELECT * FROM gtest4;
 (2 rows)
 
 DROP TABLE gtest4;
+--this will fail however
+CREATE TABLE gtest4 (
+  a int,
+  b double_int GENERATED ALWAYS AS ((a * 2, a * 3)::double_int) VIRTUAL
+);
+ERROR:  generation expression uses user-defined type
+LINE 3:   b double_int GENERATED ALWAYS AS ((a * 2, a * 3)::double_i...
+                                                          ^
+DETAIL:  Virtual generated columns that make use of user-defined types are not yet supported.
 DROP TYPE double_int;
 -- using tableoid is allowed
 CREATE TABLE gtest_tableoid (
diff --git a/src/test/regress/sql/create_type.sql b/src/test/regress/sql/create_type.sql
index c25018029c2..b126ce88367 100644
--- a/src/test/regress/sql/create_type.sql
+++ b/src/test/regress/sql/create_type.sql
@@ -103,6 +103,10 @@ CREATE TYPE text_w_default (
    default = 'zippo'
 );
 
+--can not use user defined type for virtual generated column
+CREATE TABLE gtest1 (a int42 GENERATED ALWAYS AS ('1') VIRTUAL);
+CREATE TABLE gtest1 (a int GENERATED ALWAYS AS ('1'::int42) VIRTUAL);
+
 CREATE TABLE default_test (f1 text_w_default, f2 int42);
 
 INSERT INTO default_test DEFAULT VALUES;
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index ae5c1c5f8f8..a7edcd3be61 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -257,6 +257,11 @@ INSERT INTO gtest4 VALUES (1), (6);
 SELECT * FROM gtest4;
 
 DROP TABLE gtest4;
+--this will fail however
+CREATE TABLE gtest4 (
+  a int,
+  b double_int GENERATED ALWAYS AS ((a * 2, a * 3)::double_int) VIRTUAL
+);
 DROP TYPE double_int;
 
 -- using tableoid is allowed
-- 
2.34.1