search

TortugaResearch / Tortuga.Chain

A fluent ORM for .NET
Other
336 stars 22 forks source link

Postgres - Passing and returning Composite type Array to Stored procedure or Functions? #399

Open maulik-modi opened 3 years ago

maulik-modi commented 3 years ago

Does Chain support Passing and returning Composite type Array to Stored procedure or Functions?

Grauenwolf commented 3 years ago

Can you send me an example of what you're looking for?

This is definitely something that Chain needs to support, but I can't promise it does today.

maulik-modi commented 3 years ago

Use CASE 1 We create composite types for sending/receiving data from user defined functions.

CREATE TYPE address ( addresstype as int, addressline1 as text, addressline2 as text, province as text, postalcode as text, suburb as text, country as text)

CREATE TYPE contact( contacttype as int, title as int, firstname as text, lastname as text, )

We pass array of address type and array of contact type to postgres functions when inserting new customer as part of single transaction.

We map postgres type to CLR type using ADO.NET Mapper at the startup NpgsqlConnection.GlobalTypeMapper.MapComposite<SomeType>("some_composite_type");

Afterwards, we can pass composite types to and from postgres functions seamlessly. https://www.npgsql.org/doc/types/basic.html

USE CASE 2 In some archive tables/record purpose/readonly, we also store composite type array as column so entire row becomes immutable.

Business case for Chain Drawback is we have to resort to ado.net code of connection management, executing query and so on. I see, this is where Chain can simplify.