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

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:

Best regards

Alex