Введение в функции в PostgreSQL
Код с занятия
drop function if exists film_total_amount;
create function film_total_amount(film_id int) returns numeric(5,2)
as $$
select
sum(p.amount) as total_amount
from
inventory i
join rental r using(inventory_id)
join payment p using(rental_id)
where
i.film_id = film_total_amount.film_id
$$ language sql;
select
f.title,
max(r.rental_date) last_rental_date,
film_total_amount(f.film_id) as total_amount
from
film f
join inventory i using(film_id)
join rental r using (inventory_id)
group by
f.film_id,
f.title
order by last_rental_date asc
limit 10;
select
f.title,
film_total_amount(f.film_id) as total_amount
from
film f
where
f.rental_rate > 2;
drop function if exists custom_coalesce;
create function custom_coalesce(v1 varchar, v2 varchar) returns varchar
as $$
select
case
when v1 is null then v2
else v1
end
$$
language sql;select
custom_coalesce(null, 'text2'),
custom_coalesce('text1', 'text2'),
custom_coalesce('text1', null),
custom_coalesce(null, null);
drop function if exists custom_coalesce;
create function custom_coalesce(v1 varchar, v2 varchar) returns varchar
as '
select
case
when v1 is not null then v1
when v2 is not null then v2
else ''nothing''
end
' language sql;
select
custom_coalesce(null, 'text2'),
custom_coalesce('text1', 'text2'),
custom_coalesce('text1', null),
custom_coalesce(null, null);
drop function if exists custom_coalesce;
create function custom_coalesce(v1 varchar, v2 varchar) returns varchar
as $texttext$
select
case
when v1 is not null then v1
when v2 is not null then v2
else '$$'
end
$texttext$ language sql;
drop function if exists custom_coalesce;
create function custom_coalesce(varchar, varchar) returns varchar
as $$
select
case
when $1 is not null then $1
when $2 is not null then $2
else 'nothing'
end
$$ language sql;
select
custom_coalesce(null, 'text2'),
custom_coalesce('text1', 'text2'),
custom_coalesce('text1', null),
custom_coalesce(null, null);
drop function if exists custom_coalesce;
create function custom_coalesce(v1 varchar, v2 varchar) returns varchar
as $$
select
case
when v1 is not null then v1
when v2 is not null then v2
else 'nothing'
end
$$ language sql;
select
custom_coalesce(v1 => null, v2 => 'text2'),
custom_coalesce(v2 => 'text1', v1 => 'text2'),
custom_coalesce(v2 => 'text1', v1 => null),
custom_coalesce(v1 => null, v2 => null);
drop function if exists custom_coalesce;
create function custom_coalesce(v1 varchar, v2 varchar, out v3 varchar) returns varchar
as $$
select
case
when v1 is not null then v1
when v2 is not null then v2
else 'nothing'
end
$$ language sql;
select
custom_coalesce(v1 => null, v2 => 'text2'),
custom_coalesce(v2 => 'text1', v1 => 'text2'),
custom_coalesce(v2 => 'text1', v1 => null),
custom_coalesce(v1 => null, v2 => null);
drop function if exists dup;
create function dup(in v1 int, out f1 int, out f2 text)
as $$
select
v1,
cast(v1 as text) || ' is text1';
select
v1,
cast(v1 as text) || ' is text2';
select
v1,
cast(v1 as text) || ' is text3';
$$ language sql;
select
*
from
dup(1);
select
*
from
dup(777);
drop function if exists rental_duration_by_inventory;
create function rental_duration_by_inventory(inventory_id int, out rental_duration int)
as $$
select
f.rental_duration
from
inventory i
join film f using (film_id)
where
i.inventory_id = rental_duration_by_inventory.inventory_id
$$ language sql;
drop function if exists new_rent;
create function new_rent(
inventory_id int,
customer_id int,
rental_date date default current_date - 1,
staff_id int default 2) returns int
as $$
INSERT INTO rental
(
rental_date,
inventory_id,
customer_id,
return_date,
staff_id,
last_update
)
values
(
rental_date,
inventory_id,
customer_id,
(rental_date + rental_duration_by_inventory(inventory_id)),
staff_id,
now()
);
select 1;
$$ language sql;
select * from rental order by rental_id desc;
select new_rent(2, 1, current_date, 1);
select new_rent(3, 1, current_date, 1);
select new_rent(4, 1, current_date);
select new_rent(10, 1);
select new_rent(inventory_id => 6, customer_id => 2);
select new_rent(customer_id => 2, staff_id => 1, inventory_id => 8);
select new_rent(customer_id => 2, staff_id => 1);
drop function actor_list_by_film;
create function actor_list_by_film(film_id int) returns setof varchar
as $$
select
a.first_name || ' ' || a.last_name as actor_name
from
film_actor fa
join actor a using (actor_id)
where
fa.film_id = actor_list_by_film.film_id;
$$ language sql;
select
*
from
actor_list_by_film(1);
drop function actor_list_by_film;
create function actor_list_by_film(film_id int) returns setof actor
as $$
select
a.*
from
film_actor fa
join actor a using (actor_id)
where
fa.film_id = actor_list_by_film.film_id;
$$ language sql;
select
*
from
actor_list_by_film(1);
drop function actor_list_by_film;
create function actor_list_by_film(film_id int) returns table(first_name varchar, last_name varchar)
as $$
select
a.first_name,
a.last_name
from
film_actor fa
join actor a using (actor_id)
where
fa.film_id = actor_list_by_film.film_id;
$$ language sql;
select
*
from
actor_list_by_film(1);