Re: Converting stored procedure from mySQL

Andreas Hjortsberg <andreas(dot)hjortsberg(at)x-change(dot)se> writes:
> So here is my function

> CREATE OR REPLACE FUNCTION sp_order_amount_per_office(accountingid IN INTEGER, code OUT VARCHAR,officetotal OUT NUMERIC) as $$
> BEGIN
> Select office.code as code, sum(transaction.countervaluecustomer) as officetotal
> FROM office
> LEFT OUTER JOIN receipt
> ON (receipt.officeid=office.officeid)
> LEFT OUTER JOIN transaction
> ON (transaction.receiptid = Receipt.receiptid) and (receipt.accountingid=accountingid)
> GROUP BY code order by code;
> END ;
> $$ LANGUAGE plpgsql;

You're getting burnt by a standard beginner gotcha: avoid using plpgsql
variables (including named parameters) that are named the same as fields
in the queries within the function. In the above example, plpgsql
replaces *every* standalone occurrence of "code" with a "$n" reference
to its "code" variable. It knows not to replace "office.code", but
that's about the extent of its intelligence.

Another problem here is that you must use SELECT INTO if you want to
assign to a plpgsql variable. As written, the above SELECT would just
discard its results.

Another problem is that the SELECT seems to be designed to return
multiple rows --- what are you expecting will happen with that?
A SELECT INTO would only store the first row's values into the
variables.

You could write the function like this, which'd fix the first two
problems, but I don't understand exactly what you're hoping to
accomplish so I don't know what to do about the third point:

CREATE OR REPLACE FUNCTION sp_order_amount_per_office(p_accountingid IN INTEGER, p_code OUT VARCHAR, p_officetotal OUT NUMERIC) as $$
BEGIN
Select office.code, sum(transaction.countervaluecustomer)
INTO p_code, p_officetotal
FROM office
LEFT OUTER JOIN receipt
ON (receipt.officeid=office.officeid)
LEFT OUTER JOIN transaction
ON (transaction.receiptid = Receipt.receiptid) and (receipt.accountingid=p_accountingid)
GROUP BY code order by code;
END ;
$$ LANGUAGE plpgsql;

regards, tom lane