oracle存储过程转PG，record兼容问题

我来答

2021-08-20

oracle部分存储过程代码
…
type dev_record is record(
id varchar2(32),
name varchar2(64));
type t_dev_type is table of dev_record index by binary_integer;
v_temp_type t_dev_type;
v_temp_record_type t_dev_type;
begin
if xxx then
select ‘1’,‘aaa’ into v_temp_record_type from dual;
v_temp_type(v_temp_type.count+1):=v_temp_record_type;
end if;
if yyy then
select ‘2’,‘bbb’ into v_temp_record_type from dual;
v_temp_type(v_temp_type.count+1):=v_temp_record_type;
end if;
for i in 1 … _temp_type.count loop
update tabname set col=‘xxx’ where id=v_temp_type(i).id;
…
end loop;
end;

转换为pg函数时有两个问题
1.PG的record如何追加？
2.多行记录的record如何一行一行取出来用？

我来答

添加附件

问题补充

3条回答

默认

最新

海潮

pg function 可以这样写:

CREATE OR REPLACE FUNCTION fun( parm CHARACTER VARYING)
    RETURNS SETOF RECORD AS
$BODY$

DECLARE
  i_rec  RECORD;
BEGIN
  FOR i_rec IN (select xxxx from t1) LOOP
     RETURN NEXT i_rec;
  END LOOP;
  RETURN;
END;

$BODY$;

BTW,这个问题描述及例子代码写的让人不想看