I am getting error in DBES procedure API. What could be the possible reason?

vConnect
, , ,
1

I have a simple procedure that returns first account record from table. It is working in psql but not working when i expose it as an API in Vahana. Below is the procedure.

create or replace function public.func_accounts(
    x_account numeric)
returns public.account
LANGUAGE plpgsql as
$$
DECLARE
    first_account public.account;
begin

        SELECT * INTO first_account
        FROM public.account
        WHERE accountno=x_account LIMIT 1;

        RETURN first_account;

end
$$;
2 Likes
2

For Vahana to consume a procedure or function. It has to return CURSOR. Your procedure is not returning the cursor.

For e.g. try below function for PostreSQL

create or replace function public.func_accounts(
    x_account numeric)
RETURNS refcursor
LANGUAGE plpgsql as
$$
DECLARE
    x_ref refcursor = 'cur';
begin
    OPEN x_ref FOR
        SELECT *
        FROM public.account
        WHERE accountno=x_account LIMIT 1;

    RETURN x_ref;
end;
$$;
3 Likes