Query behaviour differs between SELECT and FUNCTION call

Question

Background

Creating a wrapper function for a SQL statement.

Problem

A function returns 1 row, whereas the query upon which the function is based returns 100+ rows. The parameter values are identical:

SELECT * FROM as_rpt.tasc_fsa( 'ABC', '2010-01-01'::date, '2011-01-01'::date );

The tasc_fsa function is a SELECT statement with a few table joins; the function language is 'sql' rather than 'plpgsql'.

Question

What reasons would a function return a single row yet the exact same query that the function uses, when not called via the function, correctly returns over 100 rows?

Any ideas would be most helpful.

Thank you!

Answer 1

Have you use RETURN SETOF ...?

SETOF indicates you want to return more than 1 row.

I'm pretty sure you forgot to use 'RETURN SETOF'.

Answer 2

You mention the function "joins a few tables". Your example obviously doesn't. If there are joins being performed, it is possible this is causing less results.

The way the dates are being interpreted is another possibility. When you get 1 answer, is it within the range you specify?