Re: Converting stored procedure from mySQL

Thank you Tom for your quick reply!

Tom Lane wrote:
> 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:

Basicly I would like to return all the offices and the amount of their
orders even if they are null. Is it possible to add a temporary table to
the procedure and instert the data to it, and then make a select * from
my_temp_table. Can a function return the result of a temporary table?

Regards
Andreas

>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>