Call a function: a value, rows, an effect

Runs the sample. Generated for this page; it reads the connection string from the environment.

using ArxSamples.PostgreSql;
using ArxSamples.PostgreSql.Operations.Execute.Basics;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_POSTGRESQL")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_POSTGRESQL environment variable to your PostgreSQL connection string.");

await new Basic3_CallAFunction().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK.Query;
using ARX.SDK.Services;
using static ArxSamples.PostgreSql.SampleOutput;

namespace ArxSamples.PostgreSql.Operations.Execute.Basics;

/// <summary>
/// Calling a function, in three scenarios: one value (<c>Value&lt;T&gt;()</c>); rows (<c>ResultSets[0]</c>); a function
/// run for its effect (<c>void</c>: no rows). A function is the same <see cref="ArxProcedureExpression"/> with
/// <see cref="ArxProcedureType.Function"/>: ARX sends <c>SELECT * FROM</c> instead of <c>CALL</c>. To filter, order or page a
/// function's rows like a table, Retrieve reads it as a row source (RetrieveMultiple's RealCase6, Advanced8).
/// </summary>
public sealed class Basic3_CallAFunction : ISample
{
    public string Name => "execute-basics-3-call-a-function";

    public string Title => "Call a function: a value, rows, an effect";

    public async Task RunAsync(SampleContext dbContext)
    {
        var account = new ArxEntity("account");
        account["name"] = "Function Fabrics";
        account["account_number"] = "ACC-E301";
        var accountId = await dbContext.Service.CreateAsync(account);

        foreach (var (first, title) in new[] { ("Fran", "Buyer"), ("Finn", "Engineer"), ("Faye", "Director") })
        {
            var contact = new ArxEntity("contact");
            contact["first_name"] = first;
            contact["last_name"] = "Fn-E301";
            contact["job_title"] = title;
            contact["account_id"] = new ArxEntityReference("account", accountId);
            await dbContext.Service.CreateAsync(contact);
        }

        await Scenario1_OneValueAsync(dbContext, accountId);

        await Scenario2_RowsAsync(dbContext);

        await Scenario3_AnEffectAsync(dbContext, accountId);
    }

    /// <summary>1. <c>fn_contact_count(p_account_id)</c> returns a bigint.</summary>
    private static async Task Scenario1_OneValueAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("1. One value");

        var function = new ArxProcedureExpression("fn_contact_count") { ProcedureType = ArxProcedureType.Function };
        function.AddParameter("p_account_id", accountId.AsInt64());

        var count = (await dbContext.Service.ExecuteAsync(function)).Value<long>();
        Show($"{count} contacts");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT fn_contact_count(a.id)
         * FROM account a
         * WHERE a.account_number = 'ACC-E301';
         * -- Expect: 3.
         * ===========================================================
         */
    }

    /// <summary>2. <c>fn_contacts_named(p_last_name)</c> returns a table: its rows are the first result set.</summary>
    private static async Task Scenario2_RowsAsync(SampleContext dbContext)
    {
        Step("2. Rows");

        var function = new ArxProcedureExpression("fn_contacts_named") { ProcedureType = ArxProcedureType.Function };
        function.AddParameter("Fn-E301");

        var rows = (await dbContext.Service.ExecuteAsync(function)).ResultSets[0];
        Show(string.Join(", ", rows.Entities.Select(x => $"{x["first_name"]} ({x["job_title"]})")));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name, job_title
         * FROM fn_contacts_named('Fn-E301');
         * -- Expect: 3 rows: Faye Director, Finn Engineer, Fran Buyer.
         * ===========================================================
         */
    }

    /// <summary>3. <c>fn_touch_account</c> returns <c>void</c>: it's run for what it does, and returns no rows.</summary>
    private static async Task Scenario3_AnEffectAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("3. A function run for its effect");

        var function = new ArxProcedureExpression("fn_touch_account") { ProcedureType = ArxProcedureType.Function };
        function.AddParameter(accountId.AsInt64());

        var result = await dbContext.Service.ExecuteAsync(function);
        Show($"rows returned: {result.ResultSets[0].Entities.Count}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT modified_on IS NOT NULL AS touched
         * FROM account
         * WHERE account_number = 'ACC-E301';
         * -- Expect: t.
         * ===========================================================
         */
    }
}

  > 1. One value
      3 contacts
  > 2. Rows
      Faye (Director), Finn (Engineer), Fran (Buyer)
  > 3. A function run for its effect
      rows returned: 0