example 1
create or replace function
add2a(a integer,b integer) returns integer
as $$
select a + b;
$$ language 'sql';
This function is immutable(without any access to database)
函数名 add2a,函数参数确定,a integer
注意：integer放在变量名 a 后面
返回值设置：returns [data type]
as $$具体的函数执行语句，在此为一句sql select$$
末尾language设定：此处as中的具体操作仅涉及到sql
example 2
--immutable
--Function Returns a tuple
create type Pair as (x integer, y integer)
//创建一个新类型,长度为2的元组
create or replace function
mkpair(a integer, b integer)returns Pair
as $$
declare
p Pair;
begin
p.x := a;
p.y := b;
returns p;
end;
$$ language 'plpgsql';
declare语句： declare 变量名 变量类型;
begin --- end: 初步理解类似于一个transaction，之后再回来修改、确认
:= 赋值符号
example 3
--function returns a set of atomic values
create or replace function
seq(hi integer) returns setof integer
as $$
declare
i integer;
begin
i := 1;
while (i<=hi) loop
returns next i;
i := i+1;
end loop;
return;
end;
$$ language 'plpgsql';
while loop --- end loop;
loop与end loop;是while的括号，由于
PLpgSQL是一门old fashioned language
returns next i;
每次返回一个i
setof ...
return possibly more than one row
image.png
example 4 练习
create or replace function
squares(top integer) returns setof integer
as $$
declare
i integer;
begin
i := 1;
while (i<=top) loop
returns next i*i;
i:=i+1;
end loop;
returns;
end;
$$ language 'plpgsql';
John Shepherd 的代码：
create or replace type Pair as (x integer, y integer);
create or replace function
squares(top integer) returns setof integer
as $$
declare
i integer;
p Pair;
begin
for i in 1..top loop
p.x = i
p.y= i*i
return next p;
end loop;
return;
end;
$$ language 'plpgsql';
for循环的用法：
for variable in start..end, both start and end are inclusive.
image.png
example 5 开始读数据库了
--function read from database
-- and returns a set of atomic values
create or replace function
pizzaWith(_topping text) returns setof text
as $$
declare
_p Pizzas;
begin
for _p in
select p.*
from Pizza p
join Has h on h.pizza = p.id
join Topping t on h.topping = t.id
where t.name = _topping
loop
return next _p.name;
end loop;
end;
$$ language 'plpgsql';
image.png
*Use underscores to name our local variables(to avoid conflicts with environment, attribute name as instance)
