Thread: Converting stored procedure from mySQL
Hello,
I'm trying to convert one stored procedure from mysql but I have some problems to get it runing.
It's quite basic but I haven't found the answer in the docs or on the mailing lists.
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;
I can run the select query in pqAdmin and it give me the correct result, but then I try to run it inside my function as
Ialso can run independently I get the following error message
ERROR: syntax error at or near "$1" at character 25
QUERY: SELECT office.code as $1 , sum(transaction.countervaluecustomer) as $2 FROM office LEFT OUTER JOIN receipt
ON(receipt.officeid=office.officeid) LEFT OUTER JOIN transaction ON (transaction.receiptid = Receipt.receiptid) and
(receipt.accountingid=$3 ) GROUP BY $1 order by $1
CONTEXT: SQL statement in PL/PgSQL function "sp_order_amount_per_office" near line 16
It's my first day with postgre so it is probably something very simple
Regards
Andreas
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:
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
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 >
Hi, Am Freitag, 16. Juni 2006 17:39 schrieb Andreas Hjortsberg: > 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? Return to what? To the client application? In this case you should consider using a view instead of this function. As you say you're migrating from MySQL and MySQL didn't support views in older versions, maybe you came up with this workaround. Regards, Verena
Verena Ruff <lists@triosolutions.at> writes:
> Am Freitag, 16. Juni 2006 17:39 schrieb Andreas Hjortsberg:
>> Basicly I would like to return all the offices and the amount of their
>> orders even if they are null.
> Return to what? To the client application? In this case you should
> consider using a view instead of this function. As you say you're
> migrating from MySQL and MySQL didn't support views in older versions,
> maybe you came up with this workaround.
A view would work nicely. If you really want this to be a function,
it needs to return a set of rows not just one row, and ISTM you don't
even need plpgsql: a SQL-language function would be easier.
CREATE OR REPLACE FUNCTION sp_order_amount_per_office(accountingid IN INTEGER,
code OUT VARCHAR,officetotal OUT NUMERIC)
RETURNS SETOF RECORD
as $$
Select office.code, sum(transaction.countervaluecustomer)
FROM office
LEFT OUTER JOIN receipt
ON (receipt.officeid=office.officeid)
LEFT OUTER JOIN transaction
ON (transaction.receiptid = Receipt.receiptid) and
(receipt.accountingid= $1)
GROUP BY code order by code;
$$ LANGUAGE sql;
A call would look like
SELECT * FROM sp_order_amount_per_office(42);
regards, tom lane
I am creating a simple FUNCTION that returns a few columns of data from a table. Just a question, in order to get this working, is there a general type I can return, that I can use as a generic type for all my different functions? Or do I need to create a custom type for each FUNCTION?
On Sun, Jun 18, 2006 at 06:42:28PM +0200, Greg Quinn wrote: > I am creating a simple FUNCTION that returns a few columns of data from a > table. > > Just a question, in order to get this working, is there a general type I can > return, that I can use as a generic type for all my different functions? > > Or do I need to create a custom type for each FUNCTION? In 8.1 you can use OUT or INOUT parameters; in earlier versions you can create a custom type or use "record". If you return "record" then you'll need to provide a column list when you call the function. -- Michael Fuhr
I am using 8.1. Now I am trying to create my function that returns multiple columns. I have added an OUT parameter, but of what type must it be? Any type I try to return it tells me that only one column can be returned. Here is my function... CREATE OR REPLACE FUNCTION "public"."sp_getcontacts" (out myrecord varchar) RETURNS varchar AS $body$ select firstname, lastname from contacts $body$ LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; -----Original Message----- From: Michael Fuhr [mailto:mike@fuhr.org] Sent: Saturday, June 17, 2006 7:14 PM To: Greg Quinn Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Stored Procedure Question On Sun, Jun 18, 2006 at 06:42:28PM +0200, Greg Quinn wrote: > I am creating a simple FUNCTION that returns a few columns of data from a > table. > > Just a question, in order to get this working, is there a general type I can > return, that I can use as a generic type for all my different functions? > > Or do I need to create a custom type for each FUNCTION? In 8.1 you can use OUT or INOUT parameters; in earlier versions you can create a custom type or use "record". If you return "record" then you'll need to provide a column list when you call the function. -- Michael Fuhr -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.0/368 - Release Date: 6/16/2006
On Sun, Jun 18, 2006 at 07:20:59PM +0200, Greg Quinn wrote:
> I am using 8.1. Now I am trying to create my function that returns multiple
> columns. I have added an OUT parameter, but of what type must it be?
Declare an OUT parameter for each column with that column's type.
If you want to return exactly one row then omit RETURNS; if you
want to return zero or more rows then return SETOF record.
> Any type I try to return it tells me that only one column can be returned.
>
> Here is my function...
>
> CREATE OR REPLACE FUNCTION "public"."sp_getcontacts" (out myrecord varchar)
> RETURNS varchar AS
> $body$
> select firstname, lastname from contacts
> $body$
> LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
I think you're after something like this:
CREATE OR REPLACE FUNCTION sp_getcontacts(OUT firstname varchar,
OUT lastname varchar)
RETURNS SETOF record AS $$
SELECT firstname, lastname FROM contacts
$$ LANGUAGE sql STABLE;
You'd call this function like so:
SELECT * FROM sp_getcontacts();
Is that what you're looking for?
--
Michael Fuhr