MIME-Version: 1.0
References: <CAADeyWiTu2P1Y_6dPn2E+1AwSvNkwNHmT6UcFStubz0kFh9gtg@mail.gmail.com>
 <1284143.1742922449@sss.pgh.pa.us>
In-Reply-To: <1284143.1742922449@sss.pgh.pa.us>
From: Tony Shelver <tshelver@gmail.com>
Date: Wed, 26 Mar 2025 07:33:26 +0200
Message-ID: <CAG0dhZDtaeGxpOM+F744kZG63sOaPPh3Hd8C=gMta+xEfi_v5g@mail.gmail.com>
Subject: Re: How to pass a list of locations (longitude, latitude) to a
 PostgreSQL/PostGIS stored function?
To: alexander.farber@gmail.com
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000de54150631382d7a"
Archived-At: <https://www.postgresql.org/message-id/CAG0dhZDtaeGxpOM%2BF744kZG63sOaPPh3Hd8C%3DgMta%2BxEfi_v5g%40mail.gmail.com>
Precedence: bulk

--000000000000de54150631382d7a
Content-Type: text/plain; charset="UTF-8"

On Tue, 25 Mar 2025 at 19:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Alexander Farber <alexander.farber@gmail.com> writes:
> > 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 $$
>
> Postgres isn't too friendly to representing a list of locations as
> a 2-D array, because we generally don't treat arrays as being
> arrays-of-arrays, so unnest produces a set of bigints not a set
> of smaller arrays.  You might be best advised to create a composite
> type like "location (long bigint, lat bigint)" and use an array of
> that.  If you're really hot to use a 2-D array, the only construct
> I can think of that's on board with unnesting that the way you need
> is plpgsql's FOREACH SLICE syntax:
>
>
> https://www.postgresql.org/docs/devel/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY
>
> You could probably make a custom version of unnest that uses that
> and then keep your query about the same.
>
>                         regards, tom lane
>
>
>
Another approach I use is to string everything into a JSON object, and
de-serialize it in the function.

--000000000000de54150631382d7a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_quote gmail_quote_container"><div dir=
=3D"ltr" class=3D"gmail_attr">On Tue, 25 Mar 2025 at 19:07, Tom Lane &lt;<a=
 href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br></di=
v><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex">Alexander Farber &lt;<a=
 href=3D"mailto:alexander.farber@gmail.com" target=3D"_blank">alexander.far=
ber@gmail.com</a>&gt; writes:<br>
&gt; Then I am trying to add a function, which would receive a series of<br=
>
&gt; locations (longitude and latitude pairs in microdegrees) and return a =
list<br>
&gt; of lowercase 2-letter country codes, like &quot;de&quot;, &quot;pl&quo=
t;, &quot;lv&quot;:<br>
<br>
&gt;=C2=A0 =C2=A0 =C2=A0CREATE OR REPLACE FUNCTION find_countries(locations=
 BIGINT[][])<br>
&gt;=C2=A0 =C2=A0 =C2=A0RETURNS TABLE (country TEXT) AS $$<br>
<br>
Postgres isn&#39;t too friendly to representing a list of locations as<br>
a 2-D array, because we generally don&#39;t treat arrays as being<br>
arrays-of-arrays, so unnest produces a set of bigints not a set<br>
of smaller arrays.=C2=A0 You might be best advised to create a composite<br=
>
type like &quot;location (long bigint, lat bigint)&quot; and use an array o=
f<br>
that.=C2=A0 If you&#39;re really hot to use a 2-D array, the only construct=
<br>
I can think of that&#39;s on board with unnesting that the way you need<br>
is plpgsql&#39;s FOREACH SLICE syntax:<br>
<br>
<a href=3D"https://www.postgresql.org/docs/devel/plpgsql-control-structures=
.html#PLPGSQL-FOREACH-ARRAY" rel=3D"noreferrer" target=3D"_blank">https://w=
ww.postgresql.org/docs/devel/plpgsql-control-structures.html#PLPGSQL-FOREAC=
H-ARRAY</a><br>
<br>
You could probably make a custom version of unnest that uses that<br>
and then keep your query about the same.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
<br>
<br></blockquote><div><br></div><div><div dir=3D"ltr">Another approach I us=
e is to string everything into a JSON object, and de-serialize it in the fu=
nction.</div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=
=3D"ltr" class=3D"gmail_attr"><br></div></div>=C2=A0</div></div></div>

--000000000000de54150631382d7a--