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;
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);