CODEWITHSUNDEEP
c#postgresqlstored-procedures.net-coredapper
burak ramazan
burak ramazan

Reputation: 156

How to execute postgres function using dapper

I get an error when I try to call a postgre function using dapper. Where did i do wrong? I'll be glad if you can help me.

Error Message: 

 availability_list(facilityId => integer, startDate => timestamp without time zone, endDate => timestamp without time zone) does not exist"

Call postgre function using Dapper: 

var func = "public.availability_list";

var result = db.Query<ReportResponse>(
            sql: func,
            param: new { facilityId = request.FacilityId, startDate = 
            DateTime.Now, endDate = DateTime.Now },
            commandType: CommandType.StoredProcedure, 
            commandTimeout: 900) as List<ReportResponse>;

My Postgre Function: 

CREATE FUNCTION Availability_List(facilityId int, startDate date, endDate date)
RETURNS report_type[] 
AS 
$$

DECLARE
result_record report_type[];

BEGIN

result_record := array(  
SELECT...
);

RETURN result_record;

 END $$ LANGUAGE plpgsql;

Upvotes: 5

Views: 8335

Answers (3)

Roo
Roo

Reputation: 51

Try passing function parameters in lowercase, i.e. 

var result = db.Query<ReportResponse>(
            sql: func,
            param: new { facilityid = request.FacilityId, startdate = 
            DateTime.Now, enddate = DateTime.Now },
            commandType: CommandType.StoredProcedure, 
            commandTimeout: 900) as List<ReportResponse>;

That worked for me.

Upvotes: 2

Vasanth R
Vasanth R

Reputation: 212

I think this happens because you created the function Availability_List where A and L are capitalized. But then you are calling the function with all lower case. I have faced this issue already, use all in a small case. That will be better...

Upvotes: 0

Margus
Margus

Reputation: 20028

I would expect you need to specify the parameters, schema of the function and the result should match. Likely following would work and after that you can replace the return type to see if it is mapped correctly.

var result = _connection.Query<dynamic>(
    "SELECT dbo.Availability_List(@facilityId, @startDate, @endDate)", 
    new { 
        facilityId = request.FacilityId, 
        startDate = DateTime.Now, 
        endDate = DateTime.Now 
    },
    commandType: CommandType.Text,
    commandTimeout: 900);

Upvotes: 1

Related Questions