Pass a SELECT result as an argument to postgreSQL function

Question

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$

Sounds as if you are looking for LATERAL: postgresql.org/docs/current/static/sql-select.html — user330315
– user330315, Commented Jul 22, 2014 at 8:58

MrR · Accepted Answer · 2019-11-15 12:27:31Z

22

Just enclose in round brackets:

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

edited Nov 15, 2019 at 12:27

answered Mar 29, 2019 at 13:48

MrR

4756 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fwiw Over a year ago

Best answer, clearly, but could you explain why that extra pair of brackets makes such a huge difference, please?

MrR Over a year ago

The additional parentheses encapsulate the entire subquery, ensuring that the result of the subquery is treated as a single entity, regardless of whether it returns a single row or multiple rows. Without these parentheses, the SQL engine could potentially interpret the SELECT statement as multiple distinct inputs to the get_timeinstate() function. The extra layer of parentheses provides clarity to the SQL engine, explicitly stating that the entirety of the subquery result should be treated as a singular input to the function.

Clodoaldo Neto · Accepted Answer · 2014-07-22 18:06:44Z

21

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$

edited Jul 22, 2014 at 18:06

answered Jul 22, 2014 at 8:30

Clodoaldo Neto

127k30 gold badges250 silver badges274 bronze badges

5 Comments

user3824666 Over a year ago

No, I can not, because I have about 30 functions and only one query, that must be passed as a parametr to all of them. passing query result as a parametr I will be able to execute it only 1 time. And using this query in functions I will execute it 30 times. I need less time consumption.

Clodoaldo Neto Over a year ago

@user3824666 Will the functions be used at once? If not you will be passing old data (as an int array userid::int[], that is the solution). Or is it Ok to pass old data? Otherwise if the purpose is to not repeat yourself then make that query a function itself and pass the date as argument.

user3824666 Over a year ago

The functions will be used at once, but I can't understand, how to work with an array. I'm not able to use IN operator while SELECTing, but I need it. I can't belive, that's impossible to pass a column as an argument.

Clodoaldo Neto Over a year ago

@user3824666 It is possible to pass a column value. It is not possible to pass a set of values, other than in array form. I will post how.

MrR Over a year ago

What am I missing here? What's wrong with my answer below.

Abdusoli · Accepted Answer · 2020-09-03 05:38:28Z

-2

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

answered Sep 3, 2020 at 5:38

Abdusoli

6591 gold badge10 silver badges29 bronze badges

Comments

SalientBrain · Accepted Answer · 2022-04-03 20:23:16Z

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

Collectives™ on Stack Overflow

Pass a SELECT result as an argument to postgreSQL function

4 Answers 4

2 Comments

5 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

5 Comments

Comments

Comments

Linked

Related