如何将SQL Server存储过程改写为PostgreSQL函数？报错排查

原T-SQL存储过程代码 #

CREATE PROCEDURE [dbo].[test_mssql]
    (@TaskId int = NULL,
     @RootTaskId int = NULL,
     @SessionUserId int,
     @XmlParam xml = NULL,
     @OnlyOneRow bit = 0,
     @DataType varchar (20) = 'GetChilds')
AS
BEGIN
    CREATE TABLE #t (PID int, Id int, UN varchar(500))

    INSERT INTO #t (PID, Id, UN)
        SELECT osu.PID, osu.Id, u.FN 
        FROM Users u WITH (NOLOCK)
        JOIN uosu WITH (NOLOCK) ON u.UID = uosu.UID
        JOIN osu WITH (NOLOCK) ON uosu.OSUID = osu.Id
        WHERE u.IsFired_2 = 0
          AND uosu.IsPrimary = 1

    BEGIN
        SELECT PID, Id, UN 
        FROM #t
    END
END

尝试改写的PL/pgSQL函数代码 #

CREATE OR REPLACE FUNCTION test_plpgsql(
  IN TaskId integer = null,
  IN RootTaskId integer = null,
  IN SessionUserId integer = null,
  IN XmlParam xml = null,
  IN OnlyOneRow boolean = false,
  IN DataType varchar(20) = 'GetChilds'
) RETURNS TABLE (PID integer, Id integer, UN varchar(500))
AS $$
BEGIN
  CREATE TEMPORARY TABLE t (PID integer, Id integer, UN varchar(500));
  INSERT INTO t (PID, Id, UN)
    SELECT osu.PID, osu.Id, u.FN
    FROM Users u
    JOIN uosu ON u.UID = uosu.UID
    JOIN osu ON uosu.OSUID = osu.Id
    WHERE u.IsFired_2 = 0
      AND uosu.IsPrimary = 1;
  RETURN QUERY SELECT PID, Id, UN FROM t;
END;
$$ LANGUAGE plpgsql;

遇到的错误 #

POSITION: 178; SQL: ;
---> Npgsql.PostgresException: 42601: syntax error at or near "$1"

问题分析与修正 #

这个错误出在PL/pgSQL脚本中，和Npgsql数据提供程序无关。核心问题是PostgreSQL不支持用=来指定函数参数的默认值，必须使用DEFAULT关键字。此外，原脚本中创建临时表的步骤完全可以省略，直接返回查询结果更高效。

修正后的PL/pgSQL函数如下：

CREATE OR REPLACE FUNCTION test_plpgsql(
  IN TaskId integer DEFAULT NULL,
  IN RootTaskId integer DEFAULT NULL,
  IN SessionUserId integer DEFAULT NULL,
  IN XmlParam xml DEFAULT NULL,
  IN OnlyOneRow boolean DEFAULT false,
  IN DataType varchar(20) DEFAULT 'GetChilds'
) RETURNS TABLE (PID integer, Id integer, UN varchar(500))
AS $$
BEGIN
  -- 对应SQL Server的WITH (NOLOCK)，设置读未提交隔离级别
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  
  RETURN QUERY 
    SELECT osu.PID, osu.Id, u.FN AS UN
    FROM Users u
    JOIN uosu ON u.UID = uosu.UID
    JOIN osu ON uosu.OSUID = osu.Id
    WHERE u.IsFired_2 = 0
      AND uosu.IsPrimary = 1;
END;
$$ LANGUAGE plpgsql;

关键修正点： #

1. 参数默认值语法：将参数名 类型 = 默认值改为参数名 类型 DEFAULT 默认值，符合PostgreSQL语法规范。
2. 移除冗余临时表：直接通过RETURN QUERY返回查询结果，避免不必要的临时表操作，提升性能。
3. NOLOCK替代方案：用SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED模拟SQL Server的WITH (NOLOCK)行为，允许读取未提交的数据。
4. 列别名匹配：将u.FN指定别名UN，确保返回的列名与函数定义的返回表结构一致。

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