你好,我正在创建一个插入元数据的过程。我创建了类型,并将一种类型包含在另一种类型中,在过程中我正在迭代它以获取值。由于我是 PostgreSQL 的新手,有人可以帮忙告诉我如何调用该过程吗?输入参数是类型。
Create Type Form_details as(
formName character varying(100),
submittedBy numeric,
createdDate date,
updatedBy numeric,
updatedDate date,
comments character varying(500),
Sections Section[]
)
create type Section as (
sectionName character varying(100),
sectionLabel character varying(100),
sectionOrder numeric
)
我写的程序是
CREATE OR REPLACE FUNCTION form_insertion(formdetails form_details[])
RETURNS character varying AS
$BODY$
DECLARE
form_details_seq integer;
section_seq integer;
formName character varying(100);
submittedBy numeric;
createdDate date;
comments character varying(500);
formStatusId numeric;
sectionOrder numeric;
sectionName character varying(100);
sectionLabel character varying(100);
attributeId numeric;
I integer;
J integer;
begin
FOR I IN 1..formdetails.COUNT
LOOP
formName :=formdetails[I].formName;
formStatusId :=formdetails[I].formStatusId;
comments :=formdetails[I].comments;
RAISE NOTICE '%', formName;
FOR J IN 1..formdetails.Section.COUNT
LOOP
sectionName :=formdetails[I].Section[J].sectionName;
RAISE NOTICE '%', sectionName;
END LOOP ;
END LOOP;
Return formName,sectionName;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
这不是完整的过程。但我正在尝试用它进行测试。您能否告诉我我的方法是否正确,以及我如何从DB侧进行测试。我将如何传递此参数。顺便说一句,我创建的类型来自Java对象。此存储过程将从Java端调用。非常感谢任何帮助。