How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?

[Alexander Farber]

Hello dear PostgreSQL users

I have prepared a https://dbfiddle.uk/vOFXNgns  for my question and also
list my SQL code below.

I have created a countires_boundaries table, which I intend to fill with
.poly files provided at Geofabrik:

    CREATE EXTENSION IF NOT EXISTS postgis;

    CREATE TABLE IF NOT EXISTS countries_boundaries (
        country TEXT PRIMARY KEY CHECK (country ~ '^[a-z]{2}$'),
        boundary GEOMETRY(MULTIPOLYGON, 4326) NOT NULL
    );

    CREATE INDEX IF NOT EXISTS countries_boundaries_index_1
    ON countries_boundaries
    USING GIST (boundary);

Then I am trying to add a function, which would receive a series of
locations (longitude and latitude pairs in microdegrees) and return a list
of lowercase 2-letter country codes, like "de", "pl", "lv":

    CREATE OR REPLACE FUNCTION find_countries(locations BIGINT[][])
    RETURNS TABLE (country TEXT) AS $$
    SELECT DISTINCT enclosing_countries.country
    FROM unnest(locations) AS location_array(lng, lat)
    JOIN LATERAL (
        SELECT country
        FROM countries_boundaries
        -- Convert microdegrees to degrees and check if the location lies
within the country boundary.
        WHERE ST_Contains(
                  boundary,
                  ST_SetSRID(
                      ST_MakePoint(lng / 1000000.0, lat / 1000000.0),
                      4326
                  )
              )
    ) AS enclosing_countries ON TRUE;
    $$ LANGUAGE sql STABLE;

Unfortunately, this gives me the error:

    table "location_array" has 1 columns available but 2 columns specified

I have also tried:

    CREATE OR REPLACE FUNCTION find_countries(locations BIGINT[][])
    RETURNS TABLE (country TEXT) AS $$
    SELECT DISTINCT enclosing_countries.country
    FROM unnest(locations) AS location
    JOIN LATERAL (
        SELECT country
        FROM countries_boundaries
        -- Convert microdegrees to degrees and check if the location lies
within the country boundary.
        WHERE ST_Contains(
                  boundary,
                  ST_SetSRID(
                      ST_MakePoint(location[1] / 1000000.0, location[2] /
1000000.0),
                      4326
                  )
              )
    ) AS enclosing_countries ON TRUE;
    $$ LANGUAGE sql STABLE;

But that gives me the error:

    cannot subscript type bigint because it does not support subscripting

I had even more attempts at fixing my issue, but have not succeeded yet

In the long run I am trying to call the function from an ASP.Net Core 8 app
as:

    public async Task<ISet<string>> FindCountries(IEnumerable<(long lng,
long lat)> locations)
    {
        HashSet<string> countries = [];

        await retryPolicy.ExecuteAsync(async () =>
        {
            await using NpgsqlConnection connection = new(connectionString);
            await connection.OpenAsync();
            using NpgsqlCommand command = new("SELECT country FROM
find_countries(@locations)", connection);

            // convert locations into the expected format (array of BIGINT
pairs)
            List<(long lng, long lat)> locationList = [.. locations];
            long[][] locationArray = [.. locationList.Select(loc => new
long[] { loc.lng, loc.lat })];
            command.Parameters.AddWithValue("locations", locationArray);

            await using NpgsqlDataReader reader = await
command.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                string countryCode = reader.GetString(0);
                if (!string.IsNullOrWhiteSpace(countryCode))
                {
                    countries.Add(countryCode);
                }
            }
        });

        return countries;
    }

Best regards
Alex