function_returns questions

[kevin]

Hi,

I was looking for a way to test what a function returns and found ... function_returns(). 🙂

Q1: Given that function_returns() has the function args, why do we also have has_function()? Looking at the function calls with a little extra spacing highlights how little difference there is:

SELECT has_function    ('m', 'find_start_date', ARRAY[ 'varchar' ],       'find_start_date check');
select function_returns('m', 'find_start_date', array[ 'varchar' ],'date','find_start_date check');

Q2: Also, given a function like:

CREATE OR REPLACE FUNCTION find_workflow_counts (p_client_name VARCHAR default null, p_year INT default null, p_timeframe VARCHAR default null)
    RETURNS TABLE (
        download_id INT,
        client_name VARCHAR,
        source VARCHAR,
        doc_type VARCHAR,
        cnt INT
    ) ...

I find that this test works:

select function_returns('m', 'find_workflow_counts', array['varchar','int','varchar'], 'setof record', 'fr test');

But it doesn't actually test what I want. This whole exercise for me came because I was a adding columns to the "return table". So is there a way to do something like this for the return type:

    'setof record(‘’int’’,’’varchar’’,’’varchar’’,’’varchar’’,’’int’’)'   -- arg 4

or maybe overload arg4 to make a new function:

    function_returns(:schema, :func_name, :args_array, :return_array, :description)

This version would be used only when returning a "setof" (or record?), so the :return_array is what's in the record.

I don't know what the best syntax might be, but the point is to verify there are 5 columns for the record and what their types are. Thoughts?

Thanks,
Kevin

[David E. Wheeler]

On Feb 4, 2025, at 12:19, kevin <kbra...@pwhome.com> wrote:

> Hi,

Hello, and apologies for the delayed reply.

> I was looking for a way to test what a function returns and found ... function_returns(). 🙂
>
> Q1: Given that function_returns() has the function args, why do we also have has_function()?

Some people just want to check for the function without regard to its return value. Also I think `has_function()` might pre-date `function_returns()`.

> I don't know what the best syntax might be, but the point is to verify there are 5 columns for the record and what their types are. Thoughts?

Hrm, I don’t think you can specify the type of the record unless it’s pre-defined as a type (or table). So if you declared a composite type[1]:

```sql
CREATE TYPE count_coll AS (

download_id INT,
client_name VARCHAR,
source VARCHAR,
doc_type VARCHAR,
cnt INT

);
```

Then you can use has_column[2] to test each of the columns of the composite type. Then you could define the function as:

```sql

CREATE OR REPLACE FUNCTION find_workflow_counts (

...
) RETURNS count_coll;
```

And then test for that return value.

But also you might want to add some functional tests that execute the function and compare the return value to an expected value. Use row_eq[3] for that.

HTH,

David

[1]: https://www.postgresql.org/docs/current/rowtypes.html
[2]: https://pgtap.org/documentation.html#has_column
[3]: https://pgtap.org/documentation.html#row_eq