将 SELECT 结果作为参数传递给 postgreSQL 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24881926/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pass a SELECT result as an argument to postgreSQL function
提问by user3824666
I have a table "UserState" with following fields: id, userid, ctime, state, endtime. I have a simple query:
我有一个表“UserState”，其中包含以下字段：id、userid、ctime、state、endtime。我有一个简单的查询：
SELECT userid FROM "UserState" WHERE ctime>'2014-07-14'::timestamp
AND I have a plpgsql function, which must take the result of this query as an argument:
而且我有一个 plpgsql 函数，它必须以这个查询的结果作为参数：
get_timeinstate(SELECT userid FROM "UserState" WHERE ctime>'2014-07-14'::timestamp);
How to create function correctly to pass a query result as parametr there? It's necessery to understand, that the function returns another SQL result and I need to use there "IN" condition:
如何正确创建函数以将查询结果作为参数传递到那里？有必要了解，该函数返回另一个 SQL 结果，我需要在那里使用“IN”条件：
$func$
BEGIN
RETURN QUERY
SELECT
...myanotherquery...
WHERE "UserState".userid IN (HERE I NEED TO INSERT MY QUERY RESULT)
END;
$func$
采纳答案by Clodoaldo Neto
Pass the returned user_id set as array. Create the function to accept an integer array
将返回的 user_id 设置为数组。创建接受整数数组的函数
create function get_timeinstate (
user_id_set integer[],
another_param...
Then call it passing the array generated by array_agg
然后调用它传递由生成的数组 array_agg
get_timeinstate(
(
select array_agg(userid)
from "UserState"
where ctime>'2014-07-14'::timestamp
),
another_param
);
Inside the function:
函数内部：
where "UserState".userid = any (user_id_set)
BTW if you are using plpgsql you can place the query inside the function and pass just the date:
顺便说一句，如果您使用 plpgsql，您可以将查询放在函数内并只传递日期：
create function get_timeinstate (
p_ctime timestamp,
another_param...
$func$
declare
user_id_set integer[] := (
select array_agg(userid)
from "UserState"
where ctime > p_ctime
);
begin
return query
select
...myanotherquery...
where "UserState".userid = any (user_id_set)
end;
$func$
回答by MrR
Just enclose in round brackets:
只需用圆括号括起来：
get_timeinstate(
(
SELECT userid FROM "UserState" WHERE ctime>'2014-07-14'::timestamp
)
);
回答by SalientBrain
I think that accepted answer is overcomplicated Here is my self-descriptive example:
我认为接受的答案过于复杂这是我的自我描述示例：
with p as
(
select id, xyz geometry from insar1.point
)
, extent as
(
select st_extent(st_force2d(geometry)) geometry from p
)
INSERT INTO insar1.grid (geometry)
SELECT (
ST_Dump(
makegrid_2d(
--maybe you need limit 1
(SELECT e.geometry from extent e), --just use braces
--this works too:
--(ARRAY(SELECT e.geometry from extent e))[1],
100,
100
)
)
) .geom geometry
