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
|
Список | 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 по дате отправления: