Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- Enable PostGIS extension if not enabled
CREATE EXTENSION IF NOT EXISTS postgis;

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

-- Add spatial index if not exists
CREATE INDEX IF NOT EXISTS countries_boundaries_index_1
ON countries_boundaries
USING GIST (boundary);

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;

CREATE EXTENSION

CREATE TABLE

CREATE INDEX

ERROR:  table "location_array" has 1 columns available but 2 columns specified
CONTEXT:  SQL function "find_countries"