467,926 Members | 1,471 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,926 developers. It's quick & easy.

Allowing the to show only 1 record or 2 records or 3 records

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

Expand|Select|Wrap|Line Numbers
  1. CREATE or REPLACE FUNCTION public.upload_coll(
  2.    l_bilbatchno character(32),
  3.    p_filnam CHARACTER,
  4.    p_filnam1 CHARACTER,
  5.    p_filnam2 CHARACTER,
  6.    user_name CHARACTER
  7. )
  8.  
  9.    RETURNS BOOLEAN
  10.    LANGUAGE plpgsql
  11.    AS $$ 
  12.  
  13. DECLARE
  14.    l_uuid_32_long varchar(36);
  15.    l_uuid_32      varchar(32);
  16.    l_oid          varchar(32);
  17.    p_matid        varchar(15);
  18.    coltran_rec    record;
  19.  
  20. BEGIN
  21.  
  22.   if p_filnam is not NUll then
  23.      l_uuid_32_long := gen_random_uuid();
  24.    l_oid := replace(l_uuid_32_long, '-', '');
  25.  
  26.    l_uuid_32_long := gen_random_uuid();
  27.    l_uuid_32 := replace(l_uuid_32_long, '-', '');
  28.  
  29.    select * into coltran_rec from acc.coltran where bilbatchno = l_bilbatchno; 
  30.  
  31.    update acc.coltran set docstored = l_uuid_32, docname1 = p_filnam where bilbatchno = l_bilbatchno;
  32.  
  33.      INSERT into public.oxfiles
  34.       (id, data, name)
  35.    VALUES
  36.      (l_uuid_32, pg_read_binary_file(p_filnam), p_filnam);
  37.    end if;
  38.  
  39.    if p_filnam1 is not Null then
  40.       l_uuid_32_long := gen_random_uuid();
  41.       l_oid := replace(l_uuid_32_long, '-', '');
  42.      l_uuid_32_long := gen_random_uuid();
  43.      l_uuid_32 := replace(l_uuid_32_long, '-', '');
  44.  
  45.       select * into coltran_rec from acc.coltran where bilbatchno = l_bilbatchno; 
  46.  
  47.       update acc.coltran set docstored1 = l_uuid_32, docname2 = p_filnam1 where bilbatchno = l_bilbatchno;
  48.       INSERT into public.oxfiles
  49.    (id, data, name)
  50.    VALUES
  51.    (l_uuid_32, pg_read_binary_file(p_filnam1), p_filnam1);
  52.  
  53.     end if;
  54.  
  55.  
  56.    if p_filnam2 is not Null then
  57.       l_uuid_32_long := gen_random_uuid();
  58.    l_oid := replace(l_uuid_32_long, '-', '');
  59.       l_uuid_32_long := gen_random_uuid();
  60.    l_uuid_32 := replace(l_uuid_32_long, '-', '');
  61.      select * into coltran_rec from acc.coltran where bilbatchno = l_bilbatchno;
  62.  
  63.    update acc.coltran set docstored2 = l_uuid_32,docname3 = p_filnam2 where bilbatchno = l_bilbatchno;
  64.  
  65.     INSERT into public.oxfiles
  66.       (id, data, name)
  67.    VALUES
  68.       (l_uuid_32, pg_read_binary_file(p_filnam2), p_filnam2);  
  69.  
  70.    end if;
  71.    return true;
  72. END
  73. $$;
2 Weeks Ago #1
  • viewed: 1061
Share:

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by chris vettese | last post: by
reply views Thread by Tom Kaminski [MVP] | last post: by
16 posts views Thread by William Buchanan | last post: by
2 posts views Thread by Otto | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.