Hi I am creating a procedure for insertion of meta data. I created types and I included 1 type in another type and in procedure I am iterating it to get the value. Since I am new to PostgreSQL Can anyone help me on how to call the procedure. The input parameter is type
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
)
and the procedure I wrote is
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;
This is not complete procedure. But I am trying to test with this. Can you please let me know whether my approach is correct and how can I test it from DB side. How I will pass this parameter. By the way the type I created is from Java object. this procedure will be calling from Java end. Any help would be greatly appreciated.
To call the function from a SQL query, you must cast the parameters to your custom type as in the following example.
select form_insertion(array[
cast(row('Form 1', 1, current_date, 1, current_date, 'This is form 1',
array[
cast(row('section-1', 'Section One', 1) as section),
cast(row('section-2', 'Section Two', 2) as section),
cast(row('section-3', 'Section Three', 3) as section)
]
) as form_details),
cast(row('Form 2', 2, current_date, 1, current_date, 'This is form 2',
array[
cast(row('section-2', 'Section Two', 2) as section),
cast(row('section-3', 'Section Three', 3) as section)
]
) as form_details),
cast(row('Form 3', 1, current_date, 1, current_date, 'This is form 3',
array[
cast(row('section-1', 'Section One', 1) as section),
cast(row('section-3', 'Section Three', 3) as section)
]
) as form_details)
])
Note that PostgreSQL arrays don't have a .COUNT property. You can iterate through an array by index range with array_upper function:
for i IN 1..array_upper(formdetails, 1)
LOOP
-- your code here
END LOOP;
Since PostgreSQL 9.1, you can use the FOREACH statement to loop through the array:
create or replace function form_insertion(formdetails form_details[])
returns varchar as $$
declare
detail form_details;
sec section;
begin
foreach detail in array formdetails
LOOP
RAISE NOTICE '%', detail.formName;
foreach sec in array detail.sections
LOOP
raise NOTICE '%', sec.sectionName;
END LOOP;
END LOOP;
return '';
end;$$
language plpgsql;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With