PL/pgSQL часть 1
create or replace function testPLpgSQL() returns integer
as $$
declare
i integer;
m float;
t varchar;
begin
i := 1 + 2;
i := i * 2;
i := (select count(*) from film);
return i;
end;
$$
language plpgsql;select testPLpgSQL();
drop function filmAverage;
create function filmAverage(out disk_per_film float, out amount_per_film float)
as $$
declare
film_nm integer;
disk_nm integer;
amount float;
begin
film_nm := (select count(*) from film);
disk_nm := (select count(*) from inventory);
amount := (select sum(p.amount) from payment p);
disk_per_film := 1.0 * disk_nm / film_nm;
amount_per_film := amount / film_nm;
end;
$$ language plpgsql;
select filmAverage();
create function filmAverage(out disk_per_film float, out amount_per_film float, out film_title_top_amount varchar(255))
as $$
declare
film_nm integer;
disk_nm integer;
amount float;
begin
film_nm := (select count(*) from film);
disk_nm := (select count(*) from inventory);
amount := (select sum(p.amount) from payment p);
declare
film_id_total_amount integer;
begin
film_id_total_amount := (
select
film_id
from
(
select
i.film_id,
sum(p.amount) as total_amount
from
inventory i
join rental r using(inventory_id)
join payment p using(rental_id)
group by
i.film_id
order by total_amount desc
limit 1
) subquery
);
film_title_top_amount := (select title from film f where f.film_id = film_id_total_amount);
end;
disk_per_film := 1.0 * disk_nm / film_nm;
amount_per_film := amount / film_nm;
end;
$$ language plpgsql;
select filmAverage();
create or replace function testFunction(testArg integer default 0) returns integer
as $$
<<outerblock>>
declare
testArg integer := 1;
begin
<<innerblock>>
declare
testArg integer := 2;
begin
raise notice 'innerblock.testArg = %', innerblock.testArg;
raise notice 'outerblock.testArg = %', outerblock.testArg;
raise notice 'testFunction.testArg = %', testFunction.testArg;
end innerblock;
return 1;
end outerblock;
$$ language plpgsql;
select testFunction();
create function timeCheck(i integer) returns integer
as $$
begin
if current_time > '11:00:00'
then
return i * 2;
end if;
return i;
end;
$$ language plpgsql;
select timeCheck(10);
create or replace function timeCheck(i integer) returns integer
as $$
begin
if current_time > '12:00:00'
then
return i * 2;
else
return i * 3;
end if;
end;
$$ language plpgsql;
create or replace function timeCheck(i integer) returns integer
as $$
begin
if current_time > '12:00:00' then return i * 2;
elsif current_time > '11:50:00' then return i * 3;
elsif current_time > '11:40:00' then return i * 4;
--else return i * 5;
end if;
return i;
end;
$$ language plpgsql;
create or replace function timeCheck(i integer default 0) returns integer
as $$
begin
case to_char(current_timestamp, 'HH')
when '12' then i := i * 12;
when '11' then return i * 11;
when '07' then return i * 7;
else return i;
end case;
return 1;
end
$$ language plpgsql;
select timeCheck(1);
create or replace function timeCheck2(i integer) returns integer
as $$
declare
res integer := i;
t varchar;
begin
t := to_char(current_timestamp, 'HH');
case to_char(current_timestamp, 'HH')
when '01' then res := i * 2;
when '02' then res := i * 4;
else res := i * 3;
end case;
return res;
end;
$$ language plpgsql;
select timeCheck2(1);
create or replace function timeCheck2(i integer) returns integer
as $$
declare
res integer := i;
t varchar;
begin
t := to_char(current_timestamp, 'HH');
case
when to_char(current_timestamp, 'HH') in ('01', '12') then res := i * 2;
when to_char(current_timestamp, 'HH:mm:ss') like '%11%' then res := i * 11;
when '02' = to_char(current_timestamp, 'HH') then res := i * 4;
else res := i * 3;
end case;
return res;
end;
$$ language plpgsql;
create or replace function testLoop() returns integer
as $$
declare
i integer := 0;
j integer;
begin
<<outerloop>>
loop
j := 0;
<<innerloop>>
loop
j := j + 1;
-- if j = 10 then exit outerloop; end if;
exit when j = 10;
end loop;
i := i + 3;
if i >= 100 then exit; end if;
end loop;
return i;
end;
$$ language plpgsql;
select testLoop();
create or replace function testLoop() returns integer
as $$
declare
i integer := 0;
begin
<<outerloop>>
loop
i := i + 3;
if i >= 100 then exit; end if;
--if i >= 50 then continue; end if;
continue when i >= 50;
raise notice 'i = %', i;
i := i + 3;
end loop;
return i;
end;
$$ language plpgsql;
create or replace function whileTest() returns integer
as $$
declare
i integer := 0;
begin
while i < 100 loop
i := i + 3;
end loop;
return i;
end;
$$ language plpgsql;
select whileTest();
create or replace function fortest(fact integer) returns integer
as $$
declare
res integer := 1;
begin
for i in 1..fact loop
res := res * i;
end loop;
return res;
end;
$$ language plpgsql;
select fortest(5);
create or replace function fortest(fact integer) returns integer
as $$
declare
res integer := 1;
begin
for i in 1..fact by 2 loop
res := res * i;
end loop;
return res;
end;
$$ language plpgsql;
select fortest(5);
create or replace function fortest(fact integer) returns integer
as $$
declare
res integer := 1;
begin
for i in reverse fact..1 by 2 loop
res := res * i;
end loop;
return res;
end;
$$ language plpgsql;
create or replace function fortest(fact integer) returns integer
as $$
declare
res integer := 1;
begin
for i in 1..fact loop
res := res * i;
fact := fact + 1;
end loop;
return res;
end;
$$ language plpgsql;
create or replace function filmFilter(len integer) returns integer
as $$
declare
i record;
begin
for i in
select
*
from
film f
where
f.length >= len
order by f.length
loop
raise notice 'film "%" with length % minutes', i.title, i.length;
end loop;
return 1;
end;
$$ language plpgsql;
select filmFilter(170);
create or replace function filmFilter(len integer) returns integer
as $$
declare
--i film;
i film%rowtype;
begin
for i in
select
*
from
film f
where
f.length >= len
order by f.length
loop
raise notice 'film "%" with length % minutes', i.title, i.length;
end loop;
return 1;
end;
$$ language plpgsql;