How to provide Npgsql with a custom data type as a

Question

I want to pass an array of key-value pairs as an argument to a PostgreSQL function using Npgsql. I have the following type defined:

drop type if exists key_value_pair
create type key_value_pair as (
    k varchar(250),
    v text
    );

I want to write a function like so:

create or replace function persist_profile(
    _user_id integer,
    _key_value_pairs key_value_pair[]
    ) returns boolean as $$
begin;

...

return true;
end;
$$ language plpgsql;

How would I pass data from a IDictionary<string, string> object to the stored procedure using Npgsql? Is this supported? I cannot find a reference online.

Thanks!

Answer 1

I convert IDictionary to 2 dimensional array and pass this array to stored procedure via Npgsql - works fine as Npgsql supports arrays as parameters. Convert like this:

                string[][] columnsArray = new string[2][];
                Dictionary<string, string> fields = ...
                columnsArray[0] = fields.Keys.ToArray();
                columnsArray[1] = fields.Values.ToArray();

Answer 2

Seems it still doesn't support user defined object types. Alternative way is serialize object to XML and pass as parameter and parse and execute it in stored procedure.

Is there any change at support status for user defined objects?

Answer 3

Npg does support it (at least in 3.1.7)

Say you're executing something like

INSERT INTO some_table (key, value) SELECT key, value FROM UNNEST(@key_value_pairs);

where key_value_pairs is an array of your key_value_pair type, and you have a class like

class key_value_pair
{
    string key;
    string value;
}

and you have an array of those objects

key_value_pair[] params = {...};

then you add parameters to the command like

using (NpgsqlCommand cmd = new NpgsqlCommand())
{
    ...
    cmd.Parameters.Add("key_value_pairs", NpgsqlDbType.Array | NpgsqlDbType.Composite).Value = params;
    cmd.ExecuteNonQuery();
}

The key point here is setting the parameter type to NpgsqlDbType.Array | NpgsqlDbType.Composite. I haven't tried calling a function that takes a user defined type but it should work same as the above. See this for some examples.

Answer 4

Jeremy!

Unfortunately, Npgsql doesn't support custom datatypes yet. I have intention to add support for that.

This would mean to add some type of "registering" of types converters with Npgsql. This way your application would register a converter for your custom type and Npgsql would use it to send data back and forth.

More details can be get in: Npgsql/src/NpgsqlTypes/NpgsqlTypeConverters.cs on cvs.npgsql.org

Please, add a feature request about that in our project site: project.npgsql.org.

Thanks in advance.