Re: SQL SERVER migration to PostgreSql

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';