Re: SQL SERVER migration to PostgreSql

SERHAD ERDEM <serhade@xxxxxxxxxxx> · Fri, 8 Nov 2019 14:31:26 +0000

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@xxxxxxxxx>

Sent: Thursday, November 7, 2019 1:28 PM

To: pgsql-general@xxxxxxxxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxxxxxxxx>

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 ?