postgres select returning no records when a function does not return anything

Question

I am having a hard time wording this question--

i have this simple select statement that returns a record

SELECT q.qid,q.question,depth,active  
FROM q 
JOIN qp ON q.qid = qp.descendant where qid=723
order by depth

this correctly returns the 1 record it is after

however when i include a function in the select statement it no longer returns the records and I am not sure why that is.

SELECT q.qid,q.question,depth,active,(getprevquestiontemp(qid)).qid parent  
FROM q 
JOIN qp ON q.qid = qp.descendant where qid=723
order by depth

this is the function

CREATE OR REPLACE FUNCTION getprevquestiontemp(cur_qid integer)
  RETURNS SETOF q AS
$BODY$ 
SELECT q.* FROM q 
JOIN qp ON q.qid = qp.ancestor WHERE qp.descendant = cur_qid
AND depth = 1;
$BODY$
  LANGUAGE sql VOLATILE
  COST 100
  ROWS 1000;

What does the function getprevquestiontemp do? Is that a set returning function? If yes use it in the FROM clause with an outer join — user330315
– user330315, Commented Feb 11, 2020 at 15:50

score 1 · Accepted Answer · 2020-02-11 16:11:10Z

1

Don't use the function in the SELECT list, use it like a table with an outer join:

SELECT q.qid, q.question, depth, active, g.qid parent  
FROM q 
  JOIN qp ON q.qid = qp.descendant 
  LEFT JOIN getprevquestiontemp(qid) as g(qid) ON true
where qid=723
order by depth

Unrelated, but: if you declare the function as stable the optimizer could [inline]https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions() the function which might lead to better performance

edited Feb 11, 2020 at 16:11

answered Feb 11, 2020 at 16:00

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

ziggy Over a year ago

can you give a little more explanation on why the query needs to be done this way?

ziggy Over a year ago

this helped me understand it better stackoverflow.com/questions/18369778/…

Collectives™ on Stack Overflow

postgres select returning no records when a function does not return anything

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related