Array parameters to and from postgresql stored procedures using Npgsql from F#

Question

I have two SQL functions (procedures) in postgresql. They take and return array; their signatures are

create function arr_ret( x int) returns int[] as

and

create function arr_param( x int[] ) returns int as

The first function when executed returns

> ctx.Functions.ArrRet.Invoke(6);;
Executing SQL : EXEC arr_ret(6) - params
val it : Unit = ()

>

As can be seen, the signature of the invoke operation is Unit() = (); doesn't return anything. I would have expected Unit() = int list because the procedure is expected to return an array of integers.

The second function when executed

> ctx.Functions.ArrParam.Invoke( [1;2;3;4;5;6] );;
  ctx.Functions.ArrParam.Invoke( [1;2;3;4;5;6] );;
  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

stdin(22,1): error FS0501: The member or object constructor 'Invoke' takes 0 argument(s) but is here given 1. The requir
ed signature is 'SqlDataProvider<...>.dataContext.Functions.ArrParam.Result.Invoke() : SqlDataProvider<...>.dataContext.
Functions.ArrParam.Result.SprocResult'.

Npgsql is not seeing the parameter (either input or output) that is of array type. The documentation says postgresql array and composite types are supported from 3.0+ version and I am using the latest 3.2.3

[1;2;3;4;5;6] is a list. To create an array in F#, the delimeters should be [| and |] instead of [ and ]. Try replacing [1;2;3;4;5;6] with [|1;2;3;4;5;6|] and see if that solves your problem. — rmunn
– rmunn, Commented Jun 8, 2017 at 7:58
Nope that doesn't solve. The error message says 'Invoke takes 0 argument(s) . For some reason it is F#'s SqlDataProvider is blind to array type. That can be seen for both the functions (in/out param). There is something that I am missing that will map array type so that it is recognized by F# SqlDataProvider. On a related note, I am also clueless how to map custom postgresql types to F# data types. — Kiran Kuppa
– Kiran Kuppa, Commented Jun 8, 2017 at 20:42
are you sure SQLProvider supports these special types? In the past even npgqsl had some issues with these. I vaguely recall having problems with the key/value store. Maybe you can ask in SQLProvider issues? — s952163
– s952163, Commented Jun 8, 2017 at 23:24

Tomislav Markovski · Accepted Answer · 2017-06-09 20:02:03Z

You are sending a single argument of type FSharpList into a method that expects params. The way you are using it will send the entire list as a single parameter.

ctx.Functions.ArrParam.Invoke(1, 2, 3, 4, 5, 6);;

The above will send them individually as you want, but not if you pass the entire collection. The reason for this is the type provider is trying to resolve the type of object instead treating the entire array as multiple arguments. In C#, this would work fine, but not in F#.

Here's a good way to test.

In C# define this method:

public static void PrintArgs(params object[] args)
{
  foreach (var arg in args)
  {
        Console.WriteLine($"Type: {arg.GetType().Name}");
  }
}

In F# call it as:

PrintArgs(1, 2.0, true, "4")

PrintArgs([1; 2; 3; 4])

They result in:

> 
Type: Int32
Type: Double
Type: Boolean
Type: String
val it : unit = ()

> 
Type: FSharpList`1
val it : unit = ()

>

Your problem is what happens in the second call, it's actually a List that's being sent and not multiple arguments.

Collectives™ on Stack Overflow

Array parameters to and from postgresql stored procedures using Npgsql from F#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related