In-Reply-To: <003101c4c344$c741fec0$020aa8c0@igor>
References: <003101c4c344$c741fec0$020aa8c0@igor>
Mime-Version: 1.0 (Apple Message framework v619)
Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed
Message-Id: <40FE6F0D-2F3D-11D9-AA1B-000D93AE0944@sitening.com>
Content-Transfer-Encoding: quoted-printable
Cc: <pgsql-sql@postgresql.org>
From: Thomas F.O'Connell <tfo@sitening.com>
Subject: Re: Help in stored procedure
Date: Fri, 5 Nov 2004 09:13:31 -0600
To: "Igor Maciel Macaubas" <igor@providerst.com.br>

I think you want to be using SELECT INTO rather than assignment for =20
your queries.

See

http://www.postgresql.org/docs/7.4/static/plpgsql-=20
statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT
http://www.postgresql.org/docs/7.4/static/plpgsql-=20
statements.html#PLPGSQL-SELECT-INTO

The expression part of a basic assignment in PL/PgSQL is sent to be =20
executed in a SELECT, so you're basically saying "SELECT select"...

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Nov 5, 2004, at 8:35 AM, Igor Maciel Macaubas wrote:

> Hi=A0Guys,
> =A0
> I need some help on building the following stored procedure, in =20
> PL/PgSQL. If this is not the right place to ask for help in this =20
> language, please let me know.
> Here is what I want to do, my comments in red:
> =A0
> CREATE OR REPLACE FUNCTION discover_nsu(integer) RETURNS integer as '
> DECLARE
> =A0=A0=A0 nsureturn integer;
> =A0=A0=A0 nsumax integer;
> =A0=A0=A0 caixaunitid alias for $1;
> =A0=A0=A0 branchid integer;
> BEGIN
> =A0=A0=A0 branchid :=3D select t1.branch as result from =
caixa.caixaunit as t1, =20
> caixa.caixa as t2 where t2.caixaunit =3D (select caixaunit from =20
> caixa.caixaunit where t2.id =3D caixaunitid);
> -- the select above will return to me a result of one row and one =20
> column, with a integer variable inside, and will assign its result to =20=

> branchid.
>
> =A0=A0=A0 nsumax :=3D select max(nsu) from =
caixa.view_transacao_agencia_nsu =20
> where branch =3D branchid;
> --=A0here=A0i'll use the var I discovered in the last=A0select =
(branchid) =20
> and will do another select in a view (this view was previously created =
=20
> and works fine), and store the result of the query inside nsumax  var.
> =A0=A0=A0 IF (nsumax <=3D 0) OR (nsumax ISNULL) THEN
> =A0=A0=A0=A0=A0=A0=A0=A0=A0 nsureturn:=3D0;
> =A0=A0=A0 ELSE
> =A0=A0=A0=A0=A0=A0=A0=A0=A0 nsureturn:=3Dnsumax + 1;
> =A0=A0=A0 END IF;
> =A0=A0=A0 RETURN nsureturn;
> -- in the if-then-else above, i was just doing a simple test. If =20
> nsumax is equal or lower than 0, or nsumax is NULL, it'll assign 0 to =20=

> the return var. Else, it'll get the max, add one, and assign the value =
=20
> to the return var, and finally, return it =3D)
>
> =A0
> END
> ' LANGUAGE 'plpgsql';
> =A0
> Okey, the function gets created fine b/c there are no sintax erros, =20=

> the problem is when i try to execute:
> =A0
> database=3D> select discover_nsu(1);
> ERROR:=A0 syntax error at or near "select" at character 9
> QUERY:=A0 SELECT=A0 select t1.branch as result from caixa.caixaunit as =
t1, =20
> caixa.caixa as t2 where t2.caixaunit =3D (select caixaunit from =20
> caixa.cai
> xaunit where t2.id =3D=A0 $1 )
> CONTEXT:=A0 PL/pgSQL function "descobrir_nsu" line 7 at assignment
> LINE 1: SELECT=A0 select t1.branch as result from caixa.caixaunit as =
t...
> =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ^
> Well, the thing is: when I execute all the selects inside the stored =20=

> procedure manually, they'll work, proving that there are no errors on =20=

> the selects statements itself. I believe that the database cannot =20
> understand the type of the result, assuming that it's a row instead of =
=20
> a single record(??). I was looking at the PL/PgSQL reference manual =20=

> and wasn't able to figure out a solution, so here I am .. can aonyone =20=

> help me? Which type should I use to receive the return from the query? =
=20
> Are cast operations (for type conversions) supported in PL/PgSQL?
> =A0
> Thanks for all, please help!
> =A0
> Regards,
> Igor
> --
> igor@providerst.com.br
> =A0