CODEWITHSUNDEEP
c#postgresqlnpgsql
user610217
user610217

Reputation:

How to provide Npgsql with a custom data type as a parameter?

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!

Upvotes: 3

Views: 8067

Answers (5)

user3595819
user3595819

Reputation: 1

Pleasingly this feature has now been implemented, the documentation can be found at: https://www.npgsql.org/doc/types/enums_and_composites.html

As the documentation states, you need to create the type in dotnet and in postgres, then map one to the other, before you open the connection for example:

NpgsqlConnection.GlobalTypeMapper.MapEnum<LocationSourceEnum>("location_source_type");

Upvotes: 0

ubi
ubi

Reputation: 4399

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.

Upvotes: -1

dgunseli
dgunseli

Reputation: 1

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?

Upvotes: 0

Aleksey Kontsevich
Aleksey Kontsevich

Reputation: 5011

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

Upvotes: 2

Francisco Junior
Francisco Junior

Reputation: 2016

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.

Upvotes: 5

Related Questions