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
Expand|Select|Wrap|Line Numbers
- 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
- $$;