Reputation: 333
I have a table "UserState" with following fields: id, userid, ctime, state, endtime. I have a simple query:
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:
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:
$func$
BEGIN
RETURN QUERY
SELECT
...myanotherquery...
WHERE "UserState".userid IN (HERE I NEED TO INSERT MY QUERY RESULT)
END;
$func$
Upvotes: 31
Views: 29812
Reputation: 2551
Put select into round brackets or use ARRAY constructor (in this case you can change index in set-returning select) 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
Upvotes: -3
Reputation: 659
In my case I used like below to pass result of SELECT
statement.
SELECT get_timeinstate(userid) FROM "UserState" WHERE ctime>'2014-07-14'::timestamp
Upvotes: -2
Reputation: 475
Just enclose in round brackets:
get_timeinstate(
(
SELECT userid FROM "UserState" WHERE ctime>'2014-07-14'::timestamp
)
);
Upvotes: 22
Reputation: 125574
Pass the returned user_id set as array. Create the function to accept an integer array
create function get_timeinstate (
user_id_set integer[],
another_param...
Then call it passing the array generated by 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:
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$
Upvotes: 20