-4

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) 
RETURNS TABLE (
epc bigint,
stc bigint)
AS $$
BEGIN
RETURN QUERY select coutns.epc, coutns.stc

from
((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;

END;
$$ LANGUAGE plpgsql;

Example call:

select * from "testFunction10"(123);

Returns 0 rows.

Improve this question
7
  • 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 – a_horse_with_no_name Oct 31 '16 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 – user1992348 Oct 31 '16 at 22:43
  • 2
    This is a non reproducible error. Provide with code that reproduces this behaviour. – ypercubeᵀᴹ Oct 31 '16 at 23:00
  • 1
    Why are there double parentheses (( and ))? – ypercubeᵀᴹ Nov 1 '16 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ᵀᴹ Nov 1 '16 at 15:26
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.

Related:

Improve this answer

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