Re: 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

> But that will be two duplicated SELECT statement. Inefficient, right?
>
> Thanks and Regards
> Yan Cheng CHEOK
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>