How convert '' to dataset of a plpgsql function in VB.NET

Question

I was learning some basics of PostgreSQL basics. while i am trying to return a resutset from PostgreSQL stored function to a VB.NET application i stuck with the following scenarios

##--- my PostgreSQL function is

CREATE OR REPLACE FUNCTION checkLogin(
IN p_uname TEXT,IN p_pwd TEXT) RETURNS refCursor AS
$BODY$
    DECLARE ref refCursor;
    BEGIN
        OPEN ref FOR SELECT UserMaster.* FROM UserMaster WHERE username=p_uname AND userpwd=p_pwd AND coalesce(userdel,FALSE)=FALSE;
        RETUR ref;
    END;
$BODY$
LANGUAGE PLPGSQL

-- My VB function To return dataset is following

Function CheckLogin(ByVal username As String,ByVal pwd As String) As DataSet
    Try

        Dim ds As New DataSet

        Dim conString As String="Server=localhost;port=5432;Database=myDB;UserId=postgres;password=postgres"
        Dim con As NpgsqlConnection=New NpgsqlConnection(ConString) 
        con.open()
        Dim com As NpgsqlCommand=New NpgsqlCommand("select * from checkLogin('"+ username +"','"+ pwd +"')",con)
        Dim da As NpgsqlDataAdapter=New NpgsqlDataAdapter(com)
        da.Fill(ds)
        Return ds
    Catch ex As Exception
        Return Nothing
    End Try
End Function

From this function it just return 'unnamed portal 1' within a tag my question is how can i convert it into DataSet. its really helpful if anybody answer it and mention what i do wrong. I googled and read most of the article related with this. But i didn't find a proper solution for this. if there is a link please mention it to me and excuse me for this question.

thanks in advance

Answer 1

If what you want is a function that returns a set of rows (much like a table), then your function should probably return a table (see the docs, or this question for an example) instead of a refcursor.

A refcursor is a server-side object which allows you to retrieve the query results in special ways (e.g. fetching a certain number of rows at a time, resetting it). If your function returns a refcursor you will need to interact with it by sending further queries as described in the docs. It doesn't seem like your use-case warrants this complexity.

Answer 2

I changed my stored function like following

CREATE OR REPLACE FUNCTION checkLogin(
IN p_uname TEXT,IN p_pwd TEXT) RETURNS TABLE(uid INTEGER,uname CHARACTOR varying,utype integer) AS
$BODY$

    BEGIN 
        RETURN QUERY
        SELECT userId,userName,userType FROM UserMaster WHERE username=p_uname AND userpwd=p_pwd AND coalesce(userdel,FALSE)=FALSE;

    END;
$BODY$
LANGUAGE PLPGSQL

this pgSQL function works fine and returns resultset and can to convert it to dataset easily.