Feed function with result of another function in PostgreSQL

Question

I have a function that select ID of some interesting rows:

CREATE OR REPLACE FUNCTION ChannelSelection(...)
RETURNS TABLE(
    ChannelId INTEGER
) AS [...] 

It takes some arguments for filtering purpose and return a SETOF INTEGER.

I would like to use the result of this function ChannelSelection to feed another one, which accept INTEGER[] as input:

CREATE OR REPLACE FUNCTION DataP2AMean(
      _channelid INTEGER[] = NULL
)
    RETURNS TABLE(
        ChannelId INTEGER,
        ...
    )  AS
$BODY$
SELECT
    D2.ChannelId,
    ...
FROM
    ...
WHERE
    D2.ChannelId = ANY(_channelid)
    OR _channelid IS NULL

$BODY$ LANGUAGE SQL;

When I issue the following query:

SELECT *
FROM DataP2AMean(_channelid := ChannelSelection(_site := '{41WOL1,41N043}'))

I get this error:

ERROR: Function datap2amean(integer) doesn't exist
State SQL :42883 (undefined_function)

Which I do not understand because I am able to use those functions separately. It looks like it search for another signature, thus that ChannelSelection returns only one integer, but it is not the case.

What am I missing?

Answer 1

channelselection returns a table (=set) of integers. So potentially many, many rows with a single integer column. datap2amean expects a single (array) value, not a set of integer values.

Unfortunately using e.g.

select array_agg(i) from channelselection('{41WOL1,41N043}') i

would still not help, because that also returns a set of integer arrays. We know that it will only ever return a single row - but a single row with one array column is still something different than a single integer array value.

So you would need to cast the result into a single value, e.g. like this:

(select array_agg(i) from channelselection('{41WOL1,41N043}') i)::int[]

This can then be used as a parameter to the other function:

SELECT *
FROM datap2amean ( (select array_agg(i) from channelselection('{41WOL1,41N043}') i)::int[] )