如何将 SQL 查询的数组输出传递到 PostgreSQL (PL/pgSQL) 函数中？

发布于 2024-10-17 12:16:28 字数 409 浏览 1 评论 0原文

我能够在 SQL 中执行以下操作，其中将 user_ids 的“数组”传递到 SQL 查询的 where 子句中。

select * from users where id in (select user_id from profiles);

我想做同样的事情，但将“数组”传递到 PostgreSQL (PL/pgSQL) 函数中，如下所示。如何声明函数并使用函数内的“数组”？

select * from users_function(select user_id from profiles);

CREATE OR REPLACE FUNCTION users_function(....)
  RETURNS void AS
$BODY$
....

原文

I am able to do the following in SQL where an "array" of user_ids are passed into the where clause of a SQL query.

select * from users where id in (select user_id from profiles);

I would like to do the same thing but pass the "array" into a PostgreSQL (PL/pgSQL) function as shown below. How do I declare the function and work with the "array" within the function?

select * from users_function(select user_id from profiles);

CREATE OR REPLACE FUNCTION users_function(....)
  RETURNS void AS
$BODY$
....

分享到QQ

分享到微博

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

发布评论

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

硬不硬你别怂 2024-10-24 12:16:28

在函数中声明数组数据类型[]，然后使用聚合函数array_agg将select语句转换为数组。

CREATE OR REPLACE FUNCTION users_function(myints integer[])
$
 BEGIN
      -- you need to find the bounds with array_lower and array_upper
  FOR i in array_lower(myints, 1) .. array_upper(myints, 1) LOOP
     Raise Notice '%', myints[i]::integer;
  END LOOP;
 END;
$

select * from users_function(array_agg((select user_id from profiles)));

Declare an array datatype [] in the function then use the aggregate function array_agg to transform the select statement into an array.

CREATE OR REPLACE FUNCTION users_function(myints integer[])
$
 BEGIN
      -- you need to find the bounds with array_lower and array_upper
  FOR i in array_lower(myints, 1) .. array_upper(myints, 1) LOOP
     Raise Notice '%', myints[i]::integer;
  END LOOP;
 END;
$

select * from users_function(array_agg((select user_id from profiles)));

回复收藏 0 原文

箹锭⒈辈孓 2024-10-24 12:16:28

正如我上面所描述的，我无法获得 nate c 的 array_agg 方法。这是一个选项：

select * from test_array('{1,2}');

CREATE OR REPLACE FUNCTION test_array(user_ids integer[])
  RETURNS void AS
$
declare
begin
FOR i in array_lower(user_ids, 1) .. array_upper(user_ids, 1) LOOP
  RAISE NOTICE '%', user_ids[i]::integer;
END LOOP;
end
$
LANGUAGE plpgsql;

I could not get the nate c's array_agg approach as I described above. This is an option:

select * from test_array('{1,2}');

CREATE OR REPLACE FUNCTION test_array(user_ids integer[])
  RETURNS void AS
$
declare
begin
FOR i in array_lower(user_ids, 1) .. array_upper(user_ids, 1) LOOP
  RAISE NOTICE '%', user_ids[i]::integer;
END LOOP;
end
$
LANGUAGE plpgsql;

回复收藏 0 原文

~没有更多了~