I'm learning how to create functions in Postgres. Below is a function I'm trying to get to return two numbers, but no matter what I try it always returns 0 rows.

Does anyone have any idea how I could get this function to work?

In the sections containing "blah blah blah" is my original query containing numerous tables that I'm joining together and some constraints I'm setting in the where section.

CREATE OR REPLACE FUNCTION "testFunction10"( integer) 
epc bigint,
stc bigint)
AS $$
RETURN QUERY select coutns.epc, coutns.stc

((select count(DISTINCT Association.rank) as epc, count(DISTINCT
oldest_pricing."startTime") as stc

from blah blah blah

left join balh blah blah and title_id = $1) -- input goes in here

left join blah blah blah

left join blah blah blah

where blah blah blah))coutns;

$$ LANGUAGE plpgsql; 

Example call:

select * from "testFunction10"(123);

Returns 0 rows.

  • 3
    Apparently somewhere in your "blah blah blah" you have a condition that makes the query not return any rows. You need to change the "blah blah" to a condition that returns the correct result
    – user1822
    Commented Oct 31, 2016 at 22:15
  • No because when I run the query inside the function on its own I get results; it's only when I use it in the function that it returns nothing Commented Oct 31, 2016 at 22:43
  • 2
    This is a non reproducible error. Provide with code that reproduces this behaviour. Commented Oct 31, 2016 at 23:00
  • 1
    Why are there double parentheses (( and ))? Commented Nov 1, 2016 at 0:55
  • 2
    I'm voting to close this question as off-topic because the original poster refuses to share what the actual problem is....
    – Philᵀᴹ
    Commented Nov 1, 2016 at 15:26

1 Answer 1


Just an educated guess while you do not disclose your complete function code.

If the same query returns two rows outside the function body, but 0 rows if you wrap it into this function, the most likely reason is a naming conflict between one of the OUT parameters epc or stc with column names in one of your JOIN or WHERE conditions. Since these parameters are NULL by default (until assigned), any condition like epc = anything would evaluate to NULL and eliminate all rows fro the result.

Yes, the two column names in the RETURNS clause are OUT parameter names and visible inside every SQL query inside the function body:

RETURNS TABLE (epc bigint, stc bigint) AS ...

It's therefore unwise to define OUT parameter names that match column names and introduce a naming conflict this way. You might use some prefix that's never used for column names like _epc to defend against it.

If you need conflicting names, be sure to table-qualify conflicting column names in SQL queries at all times.


Not the answer you're looking for? Browse other questions tagged or ask your own question.