| From: | Yan Cheng Cheok <yccheok(at)yahoo(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Question on RETURNS TABLE example in PostgreSQL documentation |
| Date: | 2010-02-22 02:31:01 |
| Message-ID: | 442639.70941.qm@web65701.mail.ac4.yahoo.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Merlin Moncure | 2010-02-22 03:32:29 | Re: Asynchronous queries - processing listen (notify) in a procedural language |
| Previous Message | Tom Lane | 2010-02-22 02:22:31 | Re: Asynchronous queries - processing listen (notify) in a procedural language |