PostgreSQL – Exception When Calling Function Returning Local Numeric from C#

ado.netcpostgresql

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

Best Answer

It seems like you misunderstood the RETURNING clause. It should list the columns the statement should return, not the names of the variables the returned values should go into. You need an additional INTO clause for the latter.

CREATE
 OR REPLACE FUNCTION public.func_insert_item(_name character varying)
                     RETURNS bigint
                     LANGUAGE plpgsql
AS
$$
DECLARE
    _item_id bigint;
BEGIN
    INSERT INTO public.items
                (name)
                VALUES (_name)
                RETURNING <name of the id column>
                          INTO _item_id;
    
    RETURN _item_id;
END;
$$

Unfortunately you didn't provide the table's DDL, so I don't know what column could be the ID you want to return. Replace <name of the id column> with its name.

Related Question