Re: Converting stored procedure from mySQL

Thank you Tom for your quick reply!

Tom Lane wrote:
> Andreas Hjortsberg <andreas.hjortsberg@x-change.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
OUTNUMERIC) 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
>