| 2Bits |
Good day
Below is the code which i use if i have 3 rows then it works but if i choose 1 record then i get a resultset error - CREATE or REPLACE FUNCTION public.upload_coll(
-
l_bilbatchno character(32),
-
p_filnam CHARACTER,
-
p_filnam1 CHARACTER,
-
p_filnam2 CHARACTER,
-
user_name CHARACTER
-
)
-
-
RETURNS BOOLEAN
-
LANGUAGE plpgsql
-
AS $$
-
-
DECLARE
-
l_uuid_32_long varchar(36);
-
l_uuid_32 varchar(32);
-
l_oid varchar(32);
-
p_matid varchar(15);
-
coltran_rec record;
-
-
BEGIN
-
-
if p_filnam is not NUll then
-
l_uuid_32_long := gen_random_uuid();
-
l_oid := replace(l_uuid_32_long, '-', '');
-
-
l_uuid_32_long := gen_random_uuid();
-
l_uuid_32 := replace(l_uuid_32_long, '-', '');
-
-
select * into coltran_rec from acc.coltran where bilbatchno = l_bilbatchno;
-
-
update acc.coltran set docstored = l_uuid_32, docname1 = p_filnam where bilbatchno = l_bilbatchno;
-
-
INSERT into public.oxfiles
-
(id, data, name)
-
VALUES
-
(l_uuid_32, pg_read_binary_file(p_filnam), p_filnam);
-
end if;
-
-
if p_filnam1 is not Null then
-
l_uuid_32_long := gen_random_uuid();
-
l_oid := replace(l_uuid_32_long, '-', '');
-
l_uuid_32_long := gen_random_uuid();
-
l_uuid_32 := replace(l_uuid_32_long, '-', '');
-
-
select * into coltran_rec from acc.coltran where bilbatchno = l_bilbatchno;
-
-
update acc.coltran set docstored1 = l_uuid_32, docname2 = p_filnam1 where bilbatchno = l_bilbatchno;
-
INSERT into public.oxfiles
-
(id, data, name)
-
VALUES
-
(l_uuid_32, pg_read_binary_file(p_filnam1), p_filnam1);
-
-
end if;
-
-
-
if p_filnam2 is not Null then
-
l_uuid_32_long := gen_random_uuid();
-
l_oid := replace(l_uuid_32_long, '-', '');
-
l_uuid_32_long := gen_random_uuid();
-
l_uuid_32 := replace(l_uuid_32_long, '-', '');
-
select * into coltran_rec from acc.coltran where bilbatchno = l_bilbatchno;
-
-
update acc.coltran set docstored2 = l_uuid_32,docname3 = p_filnam2 where bilbatchno = l_bilbatchno;
-
-
INSERT into public.oxfiles
-
(id, data, name)
-
VALUES
-
(l_uuid_32, pg_read_binary_file(p_filnam2), p_filnam2);
-
-
end if;
-
return true;
-
END
-
$$;
| |
