Введение в процедуры в PostgreSQL
Код с занятия:
create procedure maximum(in a1 integer, inout a2 integer)
as $$
select
case
when a1 > a2
then a1
else a2
end;
$$
language sql;call maximum(1, 2);
drop procedure maximum(integer, integer);
create procedure maximum(inout a1 integer, inout a2 integer)
as $$
select
case
when a1 > a2
then a1
else a2
end;
select
case
when a1 > a2
then a1
else a2
end,
case
when a1 > a2
then a2
else a1
end
$$ language sql;
call maximum(1, 2);
create procedure maximum(in a1 integer, in a2 integer, inout a3 integer)
as $$
select
case
when a1 > a2
then
case
when a1 > a3
then a1
else a3
end
else
case
when a2 > a3
then a2
else a3
end
end
$$ language sql;
call maximum(1, 2, 3);
create procedure maximum(in a1 integer, in a2 integer, in a3 integer, inout a4 integer)
as $$
select
case
when a1 > a2
then
case
when a1 > a3
then a1
else a3
end
else
case
when a2 > a3
then a2
else a3
end
end
$$ language sql;
call maximum(1, 2, 3, null);
select
*
from
pg_proc
where proname = 'maximum';
drop procedure maximum(integer, integer, integer, integer);
drop procedure maximum(integer, integer, integer);
create or replace procedure maximum(inout a1 integer, inout a2 integer)
as $$
select
case
-- определяем максимальное значение
when a1 > a2
then a1
else a2
end,
case
-- определяем минимальное значение
when a1 > a2
then a2
else a1
end
$$ language sql;
call maximum(1, 2);
create or replace procedure maximum(in a1 integer, in a2 integer, inout a3 integer)
as $$
select
case
when a1 > a2
then
case
when a1 > a3
then a1
else a3
end
else
case
when a2 > a3
then a2
else a3
end
end
$$ language sql;
call maximum(1, 2, 3);
drop table a;
create table a (
a float not null
);
select * from a;
select
*
from
generate_series(2, 7)
where fill_a() > 1;
drop function fill_a;
create procedure fill_a()
as $$
insert into a
select
random()
from
generate_series(1, 3);
$$ language sql;
select fill_a();
drop procedure test();
create procedure test()
as $$
select fill_a();
$$ language sql;
call test();
drop procedure test();
create procedure test()
as $$
call fill_a();
$$ language sql;
call test();
create function maximum(in a1 anyelement, in a2 anyelement, inout a3 anyelement)
as $$
select
case
when a1 > a2
then
case
when a1 > a3
then a1
else a3
end
else
case
when a2 > a3
then a2
else a3
end
end
$$ language sql;
select maximum(0.1, 0.5, 0.3);
select maximum('0.1', '0.5', '0.3');
select maximum('0.1'::text, '0.5'::text, '0.3'::text);
select maximum(0.1, 0.5, null);
select maximum(0.5, 0.5, null);
select maximum(0.5, 0.5, 0.5);
select maximum(0.5, null, null);
select maximum(null, null, null);
create or replace function maximum(in a1 anyelement, in a2 anyelement, inout a3 anyelement)
as $$
select
case
when a2 is null or a1 is not null and a1 > a2
then
case
when a3 is null or a1 > a3
then a1
else a3
end
else
case
when a3 is null or a2 > a3
then a2
else a3
end
end
$$ language sql;