如何在PostgreSQL中为自定义复杂类型创建转换规则

我已创建如下自定义类型：

CREATE TYPE Person AS (
  first varchar,
  last varchar
);

可通过以下方式创建该类型的字面量：

SELECT (PERSON '(Bob, Jones)').first;
-- 返回 'Bob'

现在我希望创建转换规则，将"first last"格式的字符串转换为Person类型，例如支持执行：

SELECT 'Bob Jones'::Person

并返回对应的Person对象。我尝试编写转换函数，但在引号处理上遇到困难，请问正确写法是什么？
我写的代码如下：

CREATE OR REPLACE FUNCTION makePerson(fullName varchar) RETURNS Person AS $$
     SELECT PERSON '(
                first := SPLIT_PART($1, ' ', 1),
                last  := SPLIT_PART($1, ' ', 2)
               )'
$$ LANGUAGE SQL;

CREATE CAST (varchar AS person) 
       WITH FUNCTION makePerson(varchar) AS ASSIGNMENT;

另外，在函数中如何转义引号来编写类似PERSON '($1, $1)'的语句？

更新：部分进展与新错误 #

我已取得部分进展，当前代码如下：

-- 执行成功
CREATE TYPE Person AS (
  first varchar,
  last varchar
);

-- 执行成功
CREATE OR REPLACE FUNCTION makePerson(fullName varchar) RETURNS Person AS $$
    SELECT SPLIT_PART($1, ' ', 1), SPLIT_PART($1, ' ', 2)
$$ LANGUAGE SQL;

-- 执行成功，返回正确结果
SELECT makePerson('bob jones');
-- 输出：
-- "makeperson" (person)
-- "(bob,jones)"

-- 执行失败
CREATE CAST (varchar AS person) WITH FUNCTION makePerson;
SELECT CAST('Bob Jones' AS Person);

执行转换时出现如下错误：

ERROR:  malformed record literal: "Bob Jones"
LINE 11: SELECT CAST('Bob Jones' AS Person)
                     ^
DETAIL:  Missing left parenthesis.

请求解决方法。

1. 引号转义与函数写法修正 #

在PostgreSQL的SQL函数中，用$$作为函数体分隔符时，内部单引号无需转义；但你之前的写法错误在于直接将$1拼接到字符串字面量里，PostgreSQL会把它当成普通字符串，而非参数。正确的做法是直接构造Person类型，而非拼接字符串：

CREATE OR REPLACE FUNCTION makePerson(fullName varchar) RETURNS Person AS $$
    -- 方法1：用ROW构造记录，自动转换为Person类型
    SELECT ROW(SPLIT_PART($1, ' ', 1), SPLIT_PART($1, ' ', 2))::Person;
    
    -- 方法2：若要拼接字符串转类型，需用参数拼接后转换
    -- SELECT ('(' || SPLIT_PART($1, ' ', 1) || ', ' || SPLIT_PART($1, ' ', 2) || ')')::Person;
$$ LANGUAGE SQL;

如果确实需要在字符串里包含单引号（比如处理带引号的名字），可以用两个单引号转义，例如：

-- 示例：处理包含单引号的名字
SELECT ('(' || 'O''Neil' || ', ' || 'Smith' || ')')::Person;

2. 解决CAST转换失败的错误 #

你遇到的错误是因为PostgreSQL默认优先使用内置的字符串转记录规则（要求字符串为(val1,val2)格式），而非你的自定义函数。需要调整CAST的优先级，明确让PostgreSQL使用你的转换函数：

-- 确保函数正确（若已正确定义可跳过）
CREATE OR REPLACE FUNCTION makePerson(fullName varchar) RETURNS Person AS $$
    SELECT ROW(SPLIT_PART($1, ' ', 1), SPLIT_PART($1, ' ', 2))::Person;
$$ LANGUAGE SQL;

-- 创建CAST时指定AS IMPLICIT，让PostgreSQL优先使用该转换
CREATE CAST (varchar AS person) WITH FUNCTION makePerson(varchar) AS IMPLICIT;

验证转换 #

现在执行以下语句即可正常工作：

SELECT 'Bob Jones'::Person;
-- 返回 (Bob,Jones)
SELECT CAST('Alice Smith' AS Person);
-- 返回 (Alice,Smith)

补充：处理带中间空格的全名 #

如果需要支持Mary Ann Smith这类带中间空格的全名（取Mary Ann为first，Smith为last），可以修改函数逻辑：

CREATE OR REPLACE FUNCTION makePerson(fullName varchar) RETURNS Person AS $$
    SELECT 
        TRIM(LEFT($1, LENGTH($1) - POSITION(' ' IN REVERSE($1)))) AS first,
        TRIM(RIGHT($1, POSITION(' ' IN REVERSE($1)) - 1)) AS last;
$$ LANGUAGE SQL;

内容的提问来源于stack exchange，提问作者David542