-3

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

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

Thanks in advance.

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
9
  • 6
    SO is NOT some magical device that automatically converts code in one language into code in another language. You should explain what this code is for, what it does and what have you tried to get your result. If it is obvious that you are stuck, only THEN you may get some help. Commented Jun 5, 2013 at 10:47
  • 1
    Thanks , mvp but I want to create one SP in which I want dynamic query execution with some condition. so please help me in this, Commented Jun 5, 2013 at 10:50
  • 4
    @mvp Ah damn I was just thinking about starting a company that just post requirements on SO and then deliver the answer to the customer. Commented Jun 5, 2013 at 10:54
  • 1
    @NLWino , I am database developer , due to some large amount of data I need transfer some data into postgre sql and want to work with it... Commented Jun 5, 2013 at 11:02
  • Can you explain what the procedure does? I have a hard time understanding it. (btw: it's either Postgres or PostgreSQL, never "postgre") Commented Jun 5, 2013 at 11:23

2 Answers 2

3

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

The concepts are totally different.

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

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).

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.

Sign up to request clarification or add additional context in comments.

2 Comments

Thanks , but would you please help in dynamic query
Appreciated the part "PG does not have "procedures", I won't keep googling for how to do those, thanks.
2

Given your comment

dynamically pass table name and it will execute dynamic query. I have day wise table .

I'd say you want to look into:

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.