PostgreSQL Provider

SQLProvider with Oracle is available via both NuGet-Packages:

• NuGet: SQLProvider.PostgreSql - Fixed Npgsql. Doesn't need resolution path. TypeProvider class: FSharp.Data.Sql.PostgreSql.SqlDataProvider
• NuGet: SQLProvider - Dynamic version. TypeProvider class: FSharp.Data.Sql.SqlDataProvider

Parameters

DatabaseVendor

From the FSharp.Data.Sql.Common.DatabaseProviderTypes enumeration. For PostgreSQL, use Common.DatabaseProviderTypes.POSTGRESQL.

let [<Literal>] dbVendor = Common.DatabaseProviderTypes.POSTGRESQL

ConnectionString

A basic connection string used to connect to PostgreSQL instance; typical connection strings for the driver apply here. See PostgreSQL Connecting Strings Documentation for a complete list of connection string options.

let [<Literal>] connString = "Host=localhost;Database=test;Username=test;Password=test"

ConnectionStringName

Instead of storing the connection string in the source code / fsx script, you can store values in the App.config file. This is the name of the connectionString key/value pair stored in App.config (TODO: confirm file name).

// found in App.config (TOOD: confirm)
let [<Literal>] connexStringName = "DefaultConnectionString"

Resolution Path

Path to search for assemblies containing database vendor-specific connections and custom types. Type the path where Npgsql.Data.dll is stored.

let [<Literal>] resPath = @"C:\Projects\Libs\Npgsql"

IndividualsAmount

Sets the count to load for each individual. See individuals for further info.

let [<Literal>] indivAmount = 1000

UseOptionTypes

If true, F# option types will be used in place of nullable database columns. If false, you will always receive the default value of the column's type, even if it is null in the database. You can also use VALUE_OPTION.

let [<Literal>] useOptTypes  = FSharp.Data.Sql.Common.NullableColumnType.OPTION

Owner

Indicates the schema or schemas to which SqlProvider will try to provide access to. Multiple schemas can be indicated and separated by commas or semicolons. Defaults to "public".

let [<Literal>] owner = "public, admin, references"


type sql =
    SqlDataProvider<
        dbVendor,
        connString,
        "",         //ConnectionNameString can be left empty
        resPath,
        indivAmount,
        useOptTypes,
        owner>

Functions and procedures

Functions are under ctx.Functions and procedures (PostgreSQL 11 and newer) under ctx.Procedures. Each has Invoke and InvokeAsync, which take the routine's IN and INOUT parameters in their order.

let ctx = sql.GetDataContext()

// A function's value
let total = ctx.Functions.OrderTotal.Invoke(orderId).ReturnValue

// A function that returns a table or a refcursor gives its rows
let rows = ctx.Functions.OrdersOf.Invoke(customerId).ReturnValue

// A procedure; its INOUT and OUT parameters are properties of the result
ctx.Procedures.CloseOrder.Invoke orderId
let next = ctx.Procedures.NextNumber.Invoke("invoice", 0).number

A function that returns a set of one column (RETURNS SETOF integer, or a table of one column) gives its first row's value; return a table of two or more columns to read the rows.