Skip site navigation (1) Skip section navigation (2)

Re: Converting stored procedure from mySQL

From: Andreas Hjortsberg <andreas(dot)hjortsberg(at)x-change(dot)se>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Converting stored procedure from mySQL
Date: 2006-06-16 15:39:11
Message-ID: 4492D09F.1030205@x-change.se (view raw or flat)
Thread:
Lists: pgsql-novice
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
> 


In response to

Responses

pgsql-novice by date

Next:From: Verena RuffDate: 2006-06-16 16:36:49
Subject: Re: Converting stored procedure from mySQL
Previous:From: Tom LaneDate: 2006-06-16 13:53:11
Subject: Re: Converting stored procedure from mySQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group