Sample Header Ad - 728x90

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