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$

http://stackoverflow.com/questions/18219991/passing-a-resultset-into-a-postgresql-function — Ilesh Patel, Jul 22 '14 at 08:23
Sounds as if you are looking for `LATERAL`: http://www.postgresql.org/docs/current/static/sql-select.html — , Jul 22 '14 at 08:58

Clodoaldo Neto · Accepted Answer · 2014-07-22T18:06:44.547

20

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 '14 at 18:06

answered Jul 22 '14 at 08:30

Clodoaldo Neto

118,695
26
233
260

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. – user3824666 Jul 22 '14 at 08:55
@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. – Clodoaldo Neto Jul 22 '14 at 10:12
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. – user3824666 Jul 22 '14 at 17:16
@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. – Clodoaldo Neto Jul 22 '14 at 17:18
1

What am I missing here? What's wrong with [my answer below](https://stackoverflow.com/a/55418885/5579463). – MrR Feb 03 '20 at 14:09

MrR · Answer 2 · 2019-11-15T12:27:31.780

18

Just enclose in round brackets:

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

edited Nov 15 '19 at 12:27

answered Mar 29 '19 at 13:48

MrR

411
5
12

3

Best answer, clearly, but could you explain why that extra pair of brackets makes such a huge difference, please? – fwiw Nov 13 '21 at 11:26
1

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. – MrR Jul 13 '23 at 12:16

score -1 · Answer 3 · answered Sep 03 '20 at 05:38

-1

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 03 '20 at 05:38

Abdusoli

661
1
8
24

SalientBrain · Answer 4 · 2022-04-03T20:23:16.023

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

Pass a SELECT result as an argument to postgreSQL function

4 Answers4