Question on RETURNS TABLE example in PostgreSQL documentation

Поиск
Список
Период
Сортировка
От Yan Cheng Cheok
Тема Question on RETURNS TABLE example in PostgreSQL documentation
Дата
Msg-id 442639.70941.qm@web65701.mail.ac4.yahoo.com
обсуждение исходный текст
Ответы Re: Question on RETURNS TABLE example in PostgreSQL documentation  (Pavel Stehule <pavel.stehule@gmail.com>)
Список pgsql-general
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;

But that will be two duplicated SELECT statement. Inefficient, right?

Thanks and Regards
Yan Cheng CHEOK





В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Asynchronous queries - processing listen (notify) in a procedural language
Следующее
От: Merlin Moncure
Дата:
Сообщение: Re: Asynchronous queries - processing listen (notify) in a procedural language