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 ?
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
adrian.klaver@aklaver.com
İ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
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 <ilyasderse@gmail.com>
Sent: Thursday, November 7, 2019 1:28 PM
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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 ?