数据库存储过程编程

该内容学习自https://blog.csdn.net/neweastsun/article/details/90316547

返回表类型

return query 方式

create function test8()
returns table(
	销售金额 money,
	销售数量 numeric
)
as $$
begin
return query select
销售.销售金额,
销售.销售数量
from 销售;
end;$$
language 'plpgsql';

select * from test8();

select test8();

该情况下会返回一列的格式

return next方式

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