2

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?

shevia
  • 37
  • 8
  • 1
    It's unclear to me what you are trying to achieve with the query. Please [edit] your question and add some sample data and the expected output based on that data. [_Formatted_](http://stackoverflow.com/help/formatting) **text** please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) –  Dec 29 '16 at 19:19
  • isn't that simply `select int_attribute from my_table where int_attribute > 1`? –  Dec 29 '16 at 22:04
  • your function expects an **array**. Why do you think you can (or should) pass it a simple integer value? Why did you define your function like that? –  Dec 29 '16 at 22:05
  • I know the function in the example is not very useful as is, and would be easily replaced with a simple query. I simplified the original function to make it readable in SO context. – shevia Dec 29 '16 at 22:17
  • You simplified the question to an extent where it doesn't make sense any longer –  Dec 29 '16 at 22:26
  • The question was "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". You focused on the usefulness of the custom function. I think I found the answer, I'll post as soon as I test it. – shevia Dec 29 '16 at 22:39

3 Answers3

3

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.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I didn't know about `unnest()` looks very useful, thanks. 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. Sorry if something is not clear enough, english is not my first language. – shevia Dec 30 '16 at 14:03
  • @shevia: After your update the question is clear enough. Clearer than most, actually. The one big omission: your Postgres version - should be declared in *every* question. Just wanted to mention there *may* be a better approach. Maybe not. Maybe a custom aggregate function? Or `tstzrange(min(lower(...), max(upper(...))`. You might want to ask another question with the *exact* details of your setup and requirements. – Erwin Brandstetter Dec 30 '16 at 16:00
0

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.

Community
  • 1
  • 1
shevia
  • 37
  • 8
-2

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);

Stevo
  • 27
  • 5