执行PostgreSQL函数时出现'tuple' object has no attribute 'encode'错误

问题重现 #

创建的SQL函数：

CREATE OR REPLACE FUNCTION views_schema.function_name(user_id integer, quiz_id integer)
RETURNS TABLE (title varchar, given_answer varchar, correct BOOLEAN, seconds INTEGER, answered_at TIMESTAMPtz, id int, quiz_id INT)
LANGUAGE SQL AS
$func$
SELECT title, given_answer, correct, seconds, answered_at, psa.id, quiz_id from public.table1 psa
INNER JOIN public.table2 psq ON psq.id = psa.question_id
WHERE user_id = (SELECT user_id from public.table3 where id = $1) AND first = True AND quiz_id = $2
ORDER by created_at DESC
$func$;

执行语句：

SELECT views_schema.function_name(598272,872)

在Azure Data Studio中返回错误：

Started executing query at Line 1
Commands completed successfully
Unhandled exception while executing query: 'tuple' object has no attribute 'encode'
Total execution time: 00:00:00.507

原因分析及解决步骤 #

1. 调用表函数的方式错误 #

返回TABLE类型的PostgreSQL函数，不能直接用SELECT 函数名()的方式调用——这种写法会把整个结果集包装成一个tuple返回，导致Azure Data Studio的PostgreSQL扩展解析时出错。

修正调用语句：改用FROM子句展开结果集：

SELECT * FROM views_schema.function_name(598272, 872);

2. 函数内部存在字段引用歧义 #

函数里的多个地方存在未明确指定来源的字段，容易引发解析混乱：

• WHERE子句中的user_id、first、quiz_id没有指定表别名，可能和函数参数混淆
• SELECT中的quiz_id未指定来源，和返回表的列名重复

修正函数定义：明确所有字段的表来源，同时给参数加前缀避免冲突：

CREATE OR REPLACE FUNCTION views_schema.function_name(p_user_id integer, p_quiz_id integer)
RETURNS TABLE (title varchar, given_answer varchar, correct BOOLEAN, seconds INTEGER, answered_at TIMESTAMPtz, id int, quiz_id INT)
LANGUAGE SQL AS
$func$
SELECT 
  psq.title, 
  psa.given_answer, 
  psa.correct, 
  psa.seconds, 
  psa.answered_at, 
  psa.id, 
  psa.quiz_id 
FROM public.table1 psa
INNER JOIN public.table2 psq ON psq.id = psa.question_id
WHERE psa.user_id = (SELECT user_id FROM public.table3 WHERE id = $1) 
  AND psa.first = True 
  AND psa.quiz_id = $2
ORDER BY psa.created_at DESC
$func$;

3. 排查客户端扩展问题 #

如果上述修正后仍报错，可能是Azure Data Studio的PostgreSQL扩展存在bug：

• 打开Azure Data Studio的扩展市场，将PostgreSQL扩展更新到最新版本
• 尝试用psql命令行客户端执行同样的查询，确认是否为客户端工具的问题

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