Exception when calling Postgresql function returning a local numeric from C#
0
votes
1
answer
2276
views
I wrote a Postgresql function to insert a row and return the value generated from the identity column. I receive the following exception when trying to call it from C#.
Npgsql.PostgresException: '42601: query has no destination for result data'
I have looked around for an answer, and it seems ExecuteScalar has worked for others, but in all the examples I've seen, it's usually when using RETURN QUERY, not a local variable. What am I missing?
Here is the function:
CREATE OR REPLACE FUNCTION public.func_insert_item(_name character varying)
RETURNS BIGINT
LANGUAGE plpgsql
AS $function$
DECLARE
_item_id BIGINT;
BEGIN
INSERT INTO public.items
(
name
)
VALUES
(
_name
)
RETURNING _item_id;
RETURN _item_id;
END;
$function$
Here is the C#:
static NpgsqlParameter CreateParameter(string name, ParameterDirection direction, string value)
{
var parameter = new NpgsqlParameter(name, NpgsqlTypes.NpgsqlDbType.Varchar, value.Length);
parameter.Direction = direction;
parameter.Value = value;
return parameter;
}
static void Main(string[] args)
{
using var connection = new NpgsqlConnection(connectionString.ToString());
connection.Open();
using var command = new NpgsqlCommand("func_insert_item", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(CreateParameter("_name", ParameterDirection.Input, name));
object itemId = command.ExecuteScalar();
}
Asked by TheCodeFiend
(3 rep)
Feb 13, 2021, 04:43 AM
Last activity: Feb 13, 2021, 06:04 AM
Last activity: Feb 13, 2021, 06:04 AM