Question on RETURNS TABLE example in PostgreSQL documentation

Hello

2010/2/22 Yan Cheng Cheok <yccheok@yahoo.com>:

The following code snippet are picked from PostgreSQL documentation :
http://www.postgresql.org/docs/current/static/plpgsql-declarations.html

CREATE FUNCTION extended_sales(p_itemno int) RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
   RETURN QUERY SELECT quantity, quantity * price FROM sales WHERE itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;

I would like modify the following function behavior slightly :

(1) Only return TABLE(quantity int, total numeric), if there is at least one row meet condition WHERE itemno = p_itemno

(2) If not, create the row, and return TABLE(quantity int, total numeric)

The only way I can think of is :

The only way I can think of to achieve (1) is :

LOOP
       SELECT quantity, quantity * price FROM sales WHERE itemno = p_itemno;

       -- Fall into creation code block.
       EXIT WHEN NOT FOUND;

       RETURN QUERY SELECT quantity, quantity * price FROM sales WHERE itemno = p_itemno;
END LOOP;

RETURN QUERY isn't final statement in procedure.

so you can

RETURN QUERY first_query;
IF NOT FOUND THEN
RETURN QUERY try_some_else
END IF;
RETURN; -- final return, go out

Regards
Pavel Stehule