SQLProvider
SQLProvider copied to clipboard
fsprojects
[Question] Are composite types defined in PostgreSQL supported by SQLProvider?
Description
Database has composite types defined which are input/output types to stored procedures. I am attempting to call the said procedure with input parameters. SqlProvider does not see the input parameter and assumes it is a void function.
Repro steps
The composite type defined in the database is
CREATE TYPE product_t AS
(name text,
product_type text)
The signature of procedure in the database is
create or replace function func_iproduct( p product_t ) returns void as
On .NET side (F#), I have created a type
type PgProductType(Name:string,ProductType:string)=
member this.Name = Name
member this.ProductType = ProductType
new() = PgProductType("","")
I believe there should be a way in which I could tell SqlProvider how to map PgProductType to product_t; not sure if this is required or such mapping is automatic. If required, an example how do I go about doing it would be helpful. Without this mapping, if I attempt to execute the procedure, the error message it reports back indicates that SqlProvider does NOT see the input parameter;
let prd = new PgProductType("F#Product","")
ctx.Functions.FuncIproduct.Invoke(prd)
stdin(29,1): error FS0501: The member or object constructor 'Invoke' takes 0 argument(s) but is here given 1. The required signature is 'SqlDataProvider<...>.dataContext.Functions.FuncIproduct.Result.Invoke() : Unit'.
Question
- Can I use postgresql composite types? If so, an illustrative example would help.The test suite PostgreSQLTests.fsx does not contain an example to map custom types (though there are examples to map pre-defined postgresql types).
- Any alternative solution short of re-implementing queries (using query expressions) foregoing the existing procedures?
Known workarounds
Directly use Npgsql
Related information
- PostgreSQL 9.6
- Win 7
I think so. I did notice that source code has array/composite commented out. Since SQLProvider makes use of Npgsql which provides complete support for custom types I was hoping that such support is transparently available - somehow.
Anyway, is there a plan in pipeline to have this support in near future?
@kirankuppa
Kinda - I have a PR for array support that's pretty much complete, I need another day or so to test it and submit it. After than I do want to tackle Postgres enums and composite types, it would be in July hopefully.
@piaste Excellent! I would like to try it as soon as it is out. Is support for composite types also included?
There was some PR for arrays, maybe it would help to this: https://github.com/fsprojects/SQLProvider/pull/450
Right, so an update... (lol @ "it would be in July hopefully")
The long and short is that doing this the 'proper' way would require a generative type provider. This is how Npgsql maps composite types (http://www.npgsql.org/doc/types/enums_and_composites.html):
enum SomeEnum { ... }
NpgsqlConnection.MapEnumGlobally<SomeEnum>();
class SomeType { ... }
NpgsqlConnection.MapCompositeGlobally<SomeType>();
and then it uses reflection under the hood to read the fields of the CLR type provided in the public methods above.
Unless I'm totally wrong, this screws us because we don't have a reified type to pass to Npgsql. (And I assume turning SQLProvider into a generative TP is distinctly off the table, at least until .NET Core supports them, which will likely take the better part of a year.)
There might be a way to hack it by using Npgsql to encode the individual fields and then building a ROW() expression representing the composite type through string concatenation, but I'm pretty sure it would break horribly once LINQ enters the picture. Like, if you have a clause that reads where some_composite_column.foo = some_other_column.foo, how could it get translated if Npgsql isn't aware of the type structure?
For enums, I'm currently looking into a partial implementation - just emit a module with static fields for each enum value. So you'd at least be able to navigate dataContext.Enums.Animals.Dog. However, Animal-type columns would still appear as just regular strings, unfortunately.
So how would dataContext.Enums.Animals.Dog be different from dataContext.Animals.Individuals.Dog ?
So how would dataContext.Enums.Animals.Dog be different from dataContext.Animals.Individuals.Dog ?
Not sure I understand the question. The former is a value in an enumerated type (CREATE TYPE Animals AS ENUM('dog', 'cat');), the latter is a row in a table (CREATE TABLE Animals(animal TEXT); INSERT etc..
Enums.Animals will always contain exactly one copy of each value; Animals.Individuals will contain whatever you insert into it.
If you meant 'what is the difference between using a Postgres enum and just using a foreign key to a table of values', the difference is that Postgres stores bytes under the hood and enforces type safety (and provides ordering, if you need it):
CREATE TABLE t(a TEXT, b ANIMALS);
INSERT INTO t VALUES('dog', 'dog');
SELECT * FROM t WHERE a = b
// ERROR: operator does not exist: text = animals
Ok, sounds fair. I think there are no corresponding features in other databases.