将查询结果返回到数组中

发布于 2024-09-06 23:24:30 字数 948 浏览 3 评论 0原文

以下代码片段创建三个数组，它们被传递到 PL/R 函数中。

FOR s_id, s_latitude, s_longitude IN 
  SELECT
    s.id,
    s.latitude_decimal,
    s.longitude_decimal
  FROM
    climate.station s
  WHERE
    s.applicable AND
    s.latitude_decimal BETWEEN box.latitude_min AND box.latitude_max AND
    s.longitude_decimal BETWEEN box.longitude_min AND box.longitude_max
LOOP
  SELECT array_append( v_id, s_id ) INTO v_id;
  SELECT array_append( v_latitude, s_latitude ) INTO v_latitude;
  SELECT array_append( v_longitude, s_longitude ) INTO v_longitude;
END LOOP;

数组声明为：

  v_id integer[];
  v_latitude double precision[];
  v_longitude double precision[];

我宁愿使用 CREATE TYPE 并传递一个数组，其中每个元素包含三个值。

您将如何编写此代码以使其不使用 FOR ... LOOP？

PL/R 函数的第一行是：

stations <- cbind( v_id, v_longitude, v_latitude )

我想消除该行代码并简化 SELECT 查询。

谢谢。

原文

The following code snippet creates three arrays, which are passed into a PL/R function.

FOR s_id, s_latitude, s_longitude IN 
  SELECT
    s.id,
    s.latitude_decimal,
    s.longitude_decimal
  FROM
    climate.station s
  WHERE
    s.applicable AND
    s.latitude_decimal BETWEEN box.latitude_min AND box.latitude_max AND
    s.longitude_decimal BETWEEN box.longitude_min AND box.longitude_max
LOOP
  SELECT array_append( v_id, s_id ) INTO v_id;
  SELECT array_append( v_latitude, s_latitude ) INTO v_latitude;
  SELECT array_append( v_longitude, s_longitude ) INTO v_longitude;
END LOOP;

The arrays are declared as:

  v_id integer[];
  v_latitude double precision[];
  v_longitude double precision[];

I would rather use CREATE TYPE and pass one array with each element containing three values.

How would you code this so that it does not use a FOR ... LOOP?

The first line of the PL/R function is:

stations <- cbind( v_id, v_longitude, v_latitude )

I would like to eliminate that line of code and simplify the SELECT query.

Thank you.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

木緿 2024-09-13 23:24:30

根据 pl/r 文档，“...两个-维 PostgreSQL 数组映射到 R 矩阵...”

也许类似...

  SELECT
    plr_function(
      array_agg(array[s.id,s.latitude_decimal,s.longitude_decimal])
    )
  FROM
    climate.station s
  WHERE
    s.applicable AND
    s.latitude_decimal BETWEEN box.latitude_min AND box.latitude_max AND
    s.longitude_decimal BETWEEN box.longitude_min AND box.longitude_max

According to the pl/r documentation, "...two-dimensional PostgreSQL arrays are mapped to R matrixes..."

Maybe something like...

  SELECT
    plr_function(
      array_agg(array[s.id,s.latitude_decimal,s.longitude_decimal])
    )
  FROM
    climate.station s
  WHERE
    s.applicable AND
    s.latitude_decimal BETWEEN box.latitude_min AND box.latitude_max AND
    s.longitude_decimal BETWEEN box.longitude_min AND box.longitude_max

回复收藏 0 原文

瑶笙 2024-09-13 23:24:30

您可以使用以下代码并继续使用 3 个数组：

SELECT
  array_agg(s.id),
  array_agg(s.latitude_decimal),
  array_agg(s.longitude_decimal)
INTO
  v_id, v_latitude, v_longitude
FROM
  climate.station s
WHERE
  s.applicable AND
  s.latitude_decimal BETWEEN box.latitude_min AND box.latitude_max AND
  s.longitude_decimal BETWEEN box.longitude_min AND box.longitude_max

或者使用行构造函数并组装一个复合类型的数组：

SELECT
  array_agg((
    s.id,
    s.latitude_decimal,
    s.longitude_decimal
  ))
INTO
  v_id, v_latitude, v_longitude
FROM
  climate.station s
WHERE
  s.applicable AND
  s.latitude_decimal BETWEEN box.latitude_min AND box.latitude_max AND
  s.longitude_decimal BETWEEN box.longitude_min AND box.longitude_max

顺便说一句，这仅适用于 PostgreSQL 8.4+

You could use the following code and continue using 3 arrays:

SELECT
  array_agg(s.id),
  array_agg(s.latitude_decimal),
  array_agg(s.longitude_decimal)
INTO
  v_id, v_latitude, v_longitude
FROM
  climate.station s
WHERE
  s.applicable AND
  s.latitude_decimal BETWEEN box.latitude_min AND box.latitude_max AND
  s.longitude_decimal BETWEEN box.longitude_min AND box.longitude_max

or use the row constructor and assemble an array of a composite type:

SELECT
  array_agg((
    s.id,
    s.latitude_decimal,
    s.longitude_decimal
  ))
INTO
  v_id, v_latitude, v_longitude
FROM
  climate.station s
WHERE
  s.applicable AND
  s.latitude_decimal BETWEEN box.latitude_min AND box.latitude_max AND
  s.longitude_decimal BETWEEN box.longitude_min AND box.longitude_max

BTW, this will only work on PostgreSQL 8.4+

回复收藏 0 原文

~没有更多了~