如何将SQL Server存储过程与UDF迁移转换至PostgreSQL？

现有包含表、视图、函数及存储过程的SQL Server数据库，已成功将表导出迁移至PostgreSQL，但无法将用户定义函数（UDF）和存储过程转换为PostgreSQL兼容版本。

• 多个开源转换工具：包括sqlserver2pgsql、CodeProject上的SQL Server转PostgreSQL方案
• 自行编写C#转换方法：

using System;
using System.IO;
using System.Text.RegularExpressions;
using System.Collections.Generic;

public static void ConvertMSSQLToPostgreSQL(string input, string outputFilePath)
{
    // 定义SQL Server到PostgreSQL的数据类型映射
    var dataTypeMappings = new Dictionary<string, string>()
    {
        { "INT", "INTEGER" },
        { "BIGINT", "BIGINT" },
        { "SMALLINT", "SMALLINT" },
        { "FLOAT", "FLOAT" },
        { "REAL", "REAL" },
        { "NUMERIC", "NUMERIC" },
        { "MONEY", "MONEY" },
        { "BIT", "BOOLEAN" },
        { "CHAR", "CHAR" },
        { "VARCHAR", "VARCHAR" },
        { "TEXT", "TEXT" },
        { "DATE", "DATE" },
        { "TIME", "TIME" },
        { "DATETIME", "TIMESTAMP" }
    };

    // 定义SQL Server到PostgreSQL的运算符映射
    var operatorMappings = new Dictionary<string, string>()
    {
        { "=", "=" },
        { "<>", "<>" },
        { "!=", "<>" },
        { "<", "<" },
        { "<=", "<=" },
        { ">", ">" },
        { ">=", ">=" },
        { "+", "+" },
        { "-", "-" },
        { "*", "*" },
        { "/", "/" },
        { "%", "%" },
        { "AND", "AND" },
        { "OR", "OR" },
        { "NOT", "NOT" }
    };

    // 生成匹配数据类型的正则模式
    var dataTypePattern = string.Join("|", dataTypeMappings.Keys);

    // 生成匹配运算符的正则模式
    var operatorPattern = string.Join("|", operatorMappings.Keys);

    // 匹配存储过程参数的正则模式
    var parameterPattern = @"(@\w+)\s+(?:" + dataTypePattern + @")(\([^)]+\))?";

    // 匹配运算符的替换正则模式
    var operatorReplacePattern = @"(?<![\w\d])(" + operatorPattern + @")(?![\w\d])";

    // 匹配数据类型的替换正则模式
    var dataTypeReplacePattern = @"(?<![\w\d])(" + dataTypePattern + @")(?![\w\d])";

    // 替换数据类型和运算符为PostgreSQL等价形式
    var output = Regex.Replace(input, dataTypeReplacePattern, match => dataTypeMappings[match.Value]);
    output = Regex.Replace(output, operatorReplacePattern, match => operatorMappings[match.Value]);

    // 替换存储过程参数为PostgreSQL等价形式
    output = Regex.Replace(output, parameterPattern, match =>
    {
        var parameterName = match.Groups[1].Value;
        var dataType = match.Groups[2].Success ? match.Groups[2].Value.TrimStart('(').TrimEnd(')') : "VARCHAR";
        return parameterName + " " + dataTypeMappings[dataType];
    });

    // 将转换后的PostgreSQL存储过程写入文本文件
    File.WriteAllText(outputFilePath, output);
}

• 在SQL Server中创建UDF生成PostgreSQL兼容语句：

CREATE FUNCTION [dbo].[udf_sp_to_postgres](@sp_name sysname) RETURNS nvarchar(max)
AS
BEGIN
  DECLARE @schema_name sysname = OBJECT_SCHEMA_NAME(OBJECT_ID(@sp_name));
  DECLARE @sp_base_name sysname = OBJECT_NAME(OBJECT_ID(@sp_name));
  DECLARE @arg_types nvarchar(max) = '';
  DECLARE @arg_names nvarchar(max) = '';
  DECLARE @arg_modes nvarchar(max) = '';
  DECLARE @sp_def nvarchar(max) = '';

  SELECT @sp_def = definition
  FROM sys.sql_modules
  WHERE object_id = OBJECT_ID(@sp_name);

  -- 检查存储过程是否有参数
  IF (EXISTS(SELECT * FROM sys.parameters WHERE object_id = OBJECT_ID(@sp_name))) BEGIN
    SELECT @arg_types = STRING_AGG(QUOTENAME(t.name), ',')
    FROM sys.parameters p
    JOIN sys.types t ON p.system_type_id = t.system_type_id AND t.user_type_id = p.user_type_id
    WHERE p.object_id = OBJECT_ID(@sp_name)
   -- ORDER BY p.parameter_id;

    SELECT @arg_names = STRING_AGG(QUOTENAME(p.name), ',')
    FROM sys.parameters p
    WHERE p.object_id = OBJECT_ID(@sp_name)
  --  ORDER BY p.parameter_id;

    SELECT @arg_modes = STRING_AGG(CASE p.is_output
                                    WHEN 1 THEN 'OUT'
                                    ELSE 'IN'
                                  END + ' ' + QUOTENAME(p.name) + ' ' + QUOTENAME(t.name), ',')
    FROM sys.parameters p
    JOIN sys.types t ON p.system_type_id = t.system_type_id AND t.user_type_id = p.user_type_id
    WHERE p.object_id = OBJECT_ID(@sp_name)
   -- ORDER BY p.parameter_id;

    SET @arg_types = RIGHT(@arg_types, LEN(@arg_types) - 1);
    SET @arg_names = RIGHT(@arg_names, LEN(@arg_names) - 1);
    SET @arg_modes = RIGHT(@arg_modes, LEN(@arg_modes) - 1);
  END;

  RETURN 'CREATE OR REPLACE FUNCTION ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@sp_base_name) +
         '(' + @arg_modes + ') RETURNS void AS $$' + REPLACE(@sp_def, '$', '$$') +
         '$$ LANGUAGE plpgsql;';
END;

• 视频教程中的SQL Server脚本生成方法

希望通过存储过程或C#库完成SQL Server UDF和存储过程到PostgreSQL的转换工作。

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