SQL SERVER migration to PostgreSql

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL SERVER migration to PostgreSql

İlyas Derse
I'm trying to migration to PostgreSql from SQL Server. I have Stored Procedures what have output parameters and returning tables.But you know what, we can not returning tables in stored procedures in PostgreSql and we can not use output parameters in functions in PostgreSql.

So i did not find to solves this problem. Anybody have an idea ?
Reply | Threaded
Open this post in threaded view
|

Re: SQL SERVER migration to PostgreSql

Adrian Klaver-4
On 11/7/19 5:28 AM, İlyas Derse wrote:
> I'm trying to migration to PostgreSql from SQL Server. I have Stored
> Procedures what have output parameters and returning tables.But you know
> what, we can not returning tables in stored procedures in PostgreSql and
> we can not use output parameters in functions in PostgreSql.

What version of Postgres?

>
> So i did not find to solves this problem. Anybody have an idea ?


Start here:

https://www.postgresql.org/docs/11/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS

https://www.postgresql.org/docs/11/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS

https://www.postgresql.org/docs/11/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

If that does not help then post an example of what you are trying to do.


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: SQL SERVER migration to PostgreSql

Thomas Kellerer
In reply to this post by İlyas Derse
İlyas Derse schrieb am 07.11.2019 um 14:28:
> I'm trying to migration to PostgreSql from SQL Server. I have Stored
> Procedures what have output parameters and returning tables.But you
> know what, we can not returning tables in stored procedures in
> PostgreSql and we can not use output parameters in functions in
> PostgreSql.

The correct migration path is to rewrite them to set-returning functions and use them in the FROM clause:

so instead of

   sp_foobar 42;

use

  select *
  from fn_foobar(42);

Thomas


Reply | Threaded
Open this post in threaded view
|

Re: SQL SERVER migration to PostgreSql

SERHAD ERDEM
In reply to this post by İlyas Derse

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR)

RETURNS TABLE (

film_title VARCHAR,

film_release_year INT

)

AS $$

BEGIN

RETURN QUERY SELECT

title,

cast( release_year as integer)

FROM

film

WHERE

title ILIKE p_pattern ;

END; $$

 

LANGUAGE 'plpgsql';

 

 

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR,p_year INT)

RETURNS TABLE (

film_title VARCHAR,

film_release_year INT

) AS $$

DECLARE

    var_r record;

BEGIN

FOR var_r IN(SELECT

title,

release_year

                FROM film

WHERE title ILIKE p_pattern AND

release_year = p_year)  

LOOP

        film_title := upper(var_r.title) ;

film_release_year := var_r.release_year;

        RETURN NEXT;

END LOOP;

END; $$

LANGUAGE 'plpgsql';



From: İlyas Derse <[hidden email]>
Sent: Thursday, November 7, 2019 1:28 PM
To: [hidden email] <[hidden email]>
Subject: SQL SERVER migration to PostgreSql
 
I'm trying to migration to PostgreSql from SQL Server. I have Stored Procedures what have output parameters and returning tables.But you know what, we can not returning tables in stored procedures in PostgreSql and we can not use output parameters in functions in PostgreSql.

So i did not find to solves this problem. Anybody have an idea ?