PL/pgSQL函数中'workspace_id'引用歧义错误的排查与解决

错误原因 #

错误触发点在INSERT INTO super_admins (workspace_id, user_id) VALUES (workspace_id, user_id);这一行：

• super_admins表存在名为workspace_id的列
• PL/pgSQL函数中声明了同名的局部变量workspace_id
  PostgreSQL无法判断语句中引用的是表列还是局部变量，因此抛出"column reference 'workspace_id' is ambiguous"的歧义错误。

修复方案 #

核心是让变量名与表列名明确区分，最直接的方式是给局部变量添加前缀（比如v_）避免命名冲突，修改后的完整代码如下：

CREATE TABLE IF NOT EXISTS setup_workspace_result (
  path TEXT,
  workspace_id BIGINT
);

DROP FUNCTION IF EXISTS public.setup_workspace;

CREATE OR REPLACE FUNCTION setup_workspace(refresh_token TEXT)
RETURNS setup_workspace_result
AS $$
DECLARE
  user_email TEXT := ((current_setting('request.jwt.claims'::text, TRUE))::JSON ->> 'email');
  workspace_name TEXT := ((current_setting('request.jwt.claims'::text, TRUE))::JSON ->> 'hd');
  name TEXT :=  ((current_setting('request.jwt.claims'::text, TRUE))::JSON ->> 'name');
  is_admin BOOLEAN := ((current_setting('request.jwt.claims'::text, TRUE))::JSON ->> 'isAdmin');
  v_workspace_id BIGINT; -- 重命名变量，添加v_前缀
  user_id BIGINT;
  result setup_workspace_result;
BEGIN
  -- Check if user already exists
  SELECT users.id, users.workspace_id INTO user_id, v_workspace_id FROM users WHERE email = user_email;
  IF user_id IS NOT NULL THEN
    -- User already exists, return workspace_id
    UPDATE service_google 
    SET token = refresh_token
    WHERE service_google.workspace_id = v_workspace_id;

    result.workspace_id := v_workspace_id;
    result.path := 'login';
  ELSE
    -- Insert data into tables
    INSERT INTO users (email, name)
    VALUES (user_email, name) RETURNING id INTO user_id;

    INSERT INTO workspaces (domain_name, created_by)
    VALUES (workspace_name, user_id) RETURNING id INTO v_workspace_id;

    INSERT INTO super_admins (workspace_id, user_id)
    VALUES (v_workspace_id, user_id); -- 明确引用局部变量

    result.workspace_id := v_workspace_id;
    result.path := 'signup';
  END IF;

  RETURN result;
END;
$$ LANGUAGE plpgsql;

也可以选择另一种方式：在引用变量时用variable关键字明确限定（比如VALUES (variable.workspace_id, user_id);），但给变量加前缀的方式更直观，能从根源避免类似命名冲突。

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