CODEWITHSUNDEEP
sqlarrayspostgresqlplpgsql
shevia
shevia

Reputation: 47

Use SQL query as array parameter (input) in custom function

I created a custom function in pl/pgsql and it's only input is an array. I would like to use the result of a query as the input for this custom function, but can't seem to get it right. I'll use a simpler function as an example and also a simpler query than the one I'm trying to use as input.

Function definition:

CREATE OR REPLACE FUNCTION bigger_than_ones(input_array int[])
returns SETOF int
AS
$$
DECLARE
  array_item int;
BEGIN
  FOREACH array_item in ARRAY input_array
  LOOP
    IF array_item > 1 THEN
      RETURN NEXT array_item;
    END IF;
  END LOOP;
END
$$ LANGUAGE plpgsql;

I have a table that has a column the same datatype of the array datatype. In this case an integer column:

SELECT * FROM my_table;
id | int_attribute
---+--------------
1  | 2
2  | 3
3  | 1
4  | 4
5  | 1
6  | 6
7  | 1
8  | 1
9  | 8

I would like to use a query that returns a column of that datatype as the input for my function. I've tried a few variations of this, without any luck:

SELECT * FROM bigger_than_ones(SELECT int_attribute FROM my_table);

To return:

result 
-------------
2
3
4
6
8

The reason I'm using a similarly structured function is because I need to calculate resulting merged time ranges (custom type) from multiple tables, in multiple places in my code. My best thought was to create a function that would do that, using as input all the time ranges, regardless of where I took it from, so I wouldn't have to make a more complex query each time.

Any thoughts on what I'm missing?

Upvotes: 2

Views: 3504

Answers (3)

shevia
shevia

Reputation: 47

I finally found the solution in another SO question: Store select query's output in one array in postgres

I was able to use the array() operator as follows:

SELECT * FROM bigger_than_ones(ARRAY(SELECT int_attribute FROM my_table));

And got the expected output.

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

What you found is an ARRAY constructor, which is not an operator (nor a function), but an SQL construct (an SQL syntax element). The referenced answer used the wrong term (now fixed). The difference might matter.

SELECT * FROM bigger_than_ones(ARRAY(SELECT int_attribute FROM my_table));

You could also use the basic aggregate function array_agg(), which is easier to integrate in more complex queries - but slower for the simple case:

SELECT * FROM bigger_than_ones((SELECT array_agg(int_attribute) FROM my_table));

Related:

I assume you are aware of unnest()? It could be used to radically simplify your test function:

CREATE OR REPLACE FUNCTION bigger_than_ones(input_array int[])
  RETURNS SETOF int AS
$func$
   SELECT *
   FROM   unnest(input_array) elem
   WHERE  elem > 1;
$func$  LANGUAGE sql;

Also, there is often a superior set-based approach to all of this. Constructing an array from a set just to pass it to a function may be unnecessary complication.

Upvotes: 3

Stevo
Stevo

Reputation: 27

Have you tried creating a custom type? There are collection types such as arrays and tables and there are object types similar to single row records. You can then create a variable of this custom type to use as parameters.

Here's an example:

CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10);

Upvotes: -2

Related Questions