将SQL Server存储过程转换为PostgreSQL存储过程 [英] Convert SQL Server stored procedure into PostgreSQL stored procedure

问题描述

我对PostgreSQL数据库非常陌生，我正在SQL Server 2008中开发数据库对象，但我也需要在PostgreSQL上进行一些操作。

I am very new to PostgreSQL database, I am developing database objects in SQL Server 2008 but I require some operation on PostgreSQL also.

在这里，我编写了一个SQL Server示例存储过程，需要将其转换为PostgreSQL。请使用PostgreSQL帮助我。

Here, I write one sample stored procedure of SQL Server which I need to convert into PostgreSQL. Please help me in this with PostgreSQL.

预先感谢。

下面是示例SQL Server存储过程：要求转换为PostgreSQL存储过程

Below is sample SQL Server stored procedure: require to convert into PostgreSQL stored procedure

CREATE PROCEDURE [dbo].[usp_GetData_ByTableName]
(
    @TableName NVARCHAR(MAX)    
    ,@IncludeKeepAlive BIT
    ,@RowsAffected BIGINT=0 OUTPUT
) AS

BEGIN

    SET NOCOUNT ON
    DECLARE @SQL VARCHAR(MAX)

    /****************************************************************
    Select data base on parameter.
    *****************************************************************/                          
    SET @SQL =
    '
        SELECT *FROM '+@TableName+' 
        WHERE 1=1
    '
    +
    CASE WHEN (@IncludeKeepAlive = 0)
        THEN
            '
                AND [MessageTransactionID] <> 152
            '
        ELSE
            ''
    END     

    EXECUTE SP_EXECUTESQL @SQL

    RETURN 0  
END

推荐答案

MS SQL中没有1：1的转换到PostgreSQL。

There is no 1:1 "conversion" from MS SQL to PostgreSQL.

概念完全不同。

我强烈建议您简单地重新实现该功能并利用PostgreSQL所具有的功能，而不是尝试模仿SQL Server。

I highly recommend to simply re-implement the functionality and exploit the features that PostgreSQL has, instead of trying to imitage SQL Server.

当人们尝试将一个DBMS的技术解决方案移植到另一个DBMS时，他们通常会发现一些缓慢且无法扩展的东西（对于Oracle-> SQL是正确的服务，SQL Server-> Oracle，DB2-> Oracle和SQL Server-> PostgreSQL也是如此。

When people try to port the technical solution of one DBMS to another they usually wind up with something that is slow and does not scale (this is true for Oracle -> SQL Serve, SQL Server -> Oracle, DB2 -> Oracle and SQL Server -> PostgreSQL just as well).

阅读有关存储函数的手册（PG没有程序），重新阅读您当前解决方案的规范，然后简单地使用Postgres提供的可能性来实施它。

Read the manual about stored functions (PG does not have "procedures") re-read the specification for your current solution and then simply implement it using the possibilities Postgres offers.

我确定这不是您想要的答案，但我坚信这是唯一可行的方法。

I'm sure this is not the answer you were looking for, but I strongly believe that this is the only viable way to go.

这篇关于将SQL Server存储过程转换为PostgreSQL存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！