Read a function that returns a table

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

using ArxSamples.PostgreSql;
using ArxSamples.PostgreSql.Operations.RetrieveMultiple.RealCases;

// 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 RealCase6_ReadAFunction().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.RetrieveMultiple.RealCases;

/// <summary>
/// A function that returns a table, read like a table, in three scenarios: with a named argument; filtered,
/// ordered and paged on top; and with a positional argument. Function reads run read-only: a function that writes
/// is refused unless the source says <c>AllowWrites()</c>.
/// </summary>
public sealed class RealCase6_ReadAFunction : ISample
{
    public string Name => "retrieve-multiple-real-6-read-a-function";

    public string Title => "Read a function that returns a table";

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

        foreach (var first in new[] { "Fay", "Finn", "Flo", "Fred" })
        {
            var contact = new ArxEntity("contact");
            contact["first_name"] = first;
            contact["last_name"] = "Func-R671";
            contact["email"] = $"{first.ToLowerInvariant()}.r671@example.com";
            contact["account_id"] = new ArxEntityReference("account", accountId);
            await dbContext.Service.CreateAsync(contact);
        }

        await Scenario1_ANamedArgumentAsync(dbContext, accountId);

        await Scenario2_FilteredOrderedPagedAsync(dbContext, accountId);

        await Scenario3_APositionalArgumentAsync(dbContext, accountId);
    }

    /// <summary>1. fn_contacts_of_account(p_account_id => …): the argument is a typed parameter.</summary>
    private static async Task Scenario1_ANamedArgumentAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("1. A named argument");

        var query = new ArxQueryExpression(ArxRowSource.Function("fn_contacts_of_account", ("p_account_id", accountId.AsInt64())));
        query.AddOrder("first_name", ArxOrderType.Ascending);

        var rows = await dbContext.Service.RetrieveMultipleAsync(query);
        Show(string.Join(", ", rows.Entities.Select(x => x["first_name"])));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT f.first_name
         * FROM account a, fn_contacts_of_account(a.id) f
         * WHERE a.account_number = 'ACC-R671'
         * ORDER BY f.first_name;
         * -- Expect: 4 rows, Fay, Finn, Flo, Fred.
         * ===========================================================
         */
    }

    /// <summary>2. Criteria, order and paging on top of the function, as on a table.</summary>
    private static async Task Scenario2_FilteredOrderedPagedAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("2. Filtered, ordered and paged");

        var query = new ArxQueryExpression(ArxRowSource.Function("fn_contacts_of_account", ("p_account_id", accountId.AsInt64())));
        query.Criteria.AddCondition("first_name", ArxConditionOperator.BeginsWith, "F");
        query.Criteria.AddCondition("first_name", ArxConditionOperator.NotEqual, "Flo");
        query.AddOrder("first_name", ArxOrderType.Descending);
        query.PageInfo = new ArxPagingInfo { Count = 2, ReturnTotalRecordCount = true };

        var page = await dbContext.Service.RetrieveMultipleAsync(query);
        Show($"{string.Join(", ", page.Entities.Select(x => x["first_name"]))} (page 1 of {page.TotalPageCount}, {page.TotalRecordCount} in all)");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT f.first_name
         * FROM account a, fn_contacts_of_account(a.id) f
         * WHERE a.account_number = 'ACC-R671' AND f.first_name <> 'Flo'
         * ORDER BY f.first_name DESC
         * LIMIT 2;
         * -- Expect: 2 rows, Fred, Finn (3 in all).
         * ===========================================================
         */
    }

    /// <summary>3. The same function with its argument by position.</summary>
    private static async Task Scenario3_APositionalArgumentAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("3. A positional argument");

        var rows = await dbContext.Service.RetrieveMultipleAsync(new ArxQueryExpression(ArxRowSource.Function("fn_contacts_of_account", accountId.AsInt64())));
        Show($"{rows.Entities.Count} contacts");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM contact c
         * JOIN account a ON a.id = c.account_id
         * WHERE a.account_number = 'ACC-R671';
         * -- Expect: 4.
         * ===========================================================
         */
    }
}

  > 1. A named argument
      Fay, Finn, Flo, Fred
  > 2. Filtered, ordered and paged
      Fred, Finn (page 1 of 2, 3 in all)
  > 3. A positional argument
      4 contacts