Hello 2010/2/22 Yan Cheng Cheok <yccheok@xxxxxxxxx>: > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
