PostgreSQL record的使用 存储过程保存一行数据的变量

以下示例用到的测试表

drop table if exists test;
create table test (
    id bigserial primary key,
    name varchar(20),
    age int
);

delete from test;
insert into test(name, age) 
values ('张三', 1),
('李四', 2),
('王五', 3),
('李四', 4),
('王五', 5);

1、结合游标使用，减少变量声明，用于接收一行的数据

create or replace function test1 (userName varchar(20)) returns int as
 $$
declare
    u record;
    sumAge int := 0;
    -- 声明游标
    curs cursor for select name,age from test where name = userName;
begin
   -- 打开游标
   --open curs;
   -- 读取游标数据
   for u in curs loop 
        sumAge = sumAge + u.age;
   end loop;
   -- 关闭游标
  -- close curs;
   return sumAge;
end
$$
language 'plpgsql';

2、用于接收一行数据，作用等同于table_name%ROWTYPE

create or replace function test1 (testId int8) returns int as
 $$
declare
   uRecord record;
   -- uRecord test%ROWTYPE;
   sumAge int := 0;
begin
   select id,name,age into uRecord from test where id = testId;
   return uRecord.age;
end
$$
language 'plpgsql';