How to return refcursor from plpgsql function in c#?

Question

I'm rookie to plpgsql, I've created a stored procedure to get the table data into refcursor and I'm trying to call the procedure from plpgsql and c# to check if the procedure/c# code is working fine.

Procedure:

CREATE OR REPLACE FUNCTION public.proc_get_test(out tbldata refcursor)
returns refcursor
LANGUAGE plpgsql
AS $function$
BEGIN
    open tbldata for
    select * from arc_mmstbrndgroup;
END;
$function$;

Calling procedure from plpgsql:

SELECT proc_get_test('cur');

Output:

Then tried fetching data from refcursor:

FETCH ALL FROM cur;

SQL Error [34000]: ERROR: cursor " cur " does not exist

Another way tried:

BEGIN;
SELECT proc_get_test('cur');
FETCH ALL FROM cur;
COMMIT;

Output:

C#:

var p = new PostgreSQLDynamicParameters();
p.Add("tbldata", dbType: NpgsqlDbType.Refcursor, direction: ParameterDirection.Output);

using (var multi = _connection.QueryMultiple("proc_get_test", param: p, commandType: CommandType.StoredProcedure))
{
    List<PostgresModel> dataMaster = multi.Read<PostgresModel>().AsList();

    return new ResponseModel { ResultSet = dataMaster, StatusCode = 1, StatusDescription = "Success" };
}

Api receiving only 1 row that too with null values

Can anyone help me with calling the procedure in plpgsql and c#?

Why are you trying to use a ref cursor? A simple table function would be far easier. — Jonathan Willcock
– Jonathan Willcock, Commented Nov 15, 2019 at 11:37
@JonathanWillcock I started working on postgres yesterday, I don't have much idea about it, so I was trying to get the data using refcursor like we do it in oracle. will try with table function — Shreyas Pednekar
– Shreyas Pednekar, Commented Nov 15, 2019 at 11:38
I guessed it might be to do with Oracle! This is definitely much easier to do with a function that returns table. If you need example code, I can happily supply some. — Jonathan Willcock
– Jonathan Willcock, Commented Nov 15, 2019 at 11:41
@JonathanWillcock Do I need to mention the table column names in the procedure? I can't do that everytime because we have huge tables with multiple columns — Shreyas Pednekar
– Shreyas Pednekar, Commented Nov 15, 2019 at 11:41
Generally the answer is yes. But this is a good thing. SELECT * FROM is bad practice. Specifying columns by name and type will save you many problems in the long run. — Jonathan Willcock
– Jonathan Willcock, Commented Nov 15, 2019 at 11:43

Shreyas Pednekar · Accepted Answer · 2019-11-19 09:36:11Z

So I tried creating a demo function with multiple refcursors in plpgsql like this:

CREATE OR REPLACE FUNCTION public.proc_get_multiple_cursors_test()
returns setof refcursor
LANGUAGE plpgsql
AS $$
DECLARE
    ref1 refcursor:= 'ref1';
    ref2 refcursor:= 'ref2';
BEGIN
    open ref1 for 
    select * from arc_mmstbrndgroup;
    return next ref1;

    open ref2 for 
    select * from arc_mmstbrndgroup;
    return next ref2;
END;
$$

and called this function from c# like this:

_connection.Open();
NpgsqlTransaction trans = _connection.BeginTransaction();
using (var multi = _connection.QueryMultiple("proc_get_multiple_cursors_test", null, transaction: trans, commandType: CommandType.StoredProcedure))
{
    var result = multi.Read();
    var ref1 = _connection.QueryMultiple("FETCH ALL IN ref1", null, commandType: CommandType.Text, transaction: trans);
    List<PostgresModel> dataMaster = ref1.Read<PostgresModel>().AsList();
    var ref2 = _connection.QueryMultiple("FETCH ALL IN ref2", null, commandType: CommandType.Text, transaction: trans);
    List<PostgresModel> dataMaster2 = ref2.Read<PostgresModel>().AsList();
    trans.Commit();
    _connection.Close();

    return new ResponseModel
    {
        ResultSet = new
        {
            master1 = dataMaster,
            master2 = dataMaster2
        },
        StatusCode = 1,
        StatusDescription = "Success"
    };
}

Now it's returning output of both the cursors

Collectives™ on Stack Overflow

How to return refcursor from plpgsql function in c#?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related