CODEWITHSUNDEEP
postgresqlplpgsql
user3824666
user3824666

Reputation: 333

Pass a SELECT result as an argument to postgreSQL function

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

Answers (4)

SalientBrain
SalientBrain

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

Abdusoli
Abdusoli

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

MrR
MrR

Reputation: 475

Just enclose in round brackets:

get_timeinstate(
  (
    SELECT userid FROM "UserState" WHERE ctime>'2014-07-14'::timestamp
  )
);

Upvotes: 22

Clodoaldo Neto
Clodoaldo Neto

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

Related Questions