This is a multi-part message in MIME format.
------=_NextPart_000_002E_01C4C32B.9E910250
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Guys,
I need some help on building the following stored procedure, in =
PL/PgSQL. If this is not the right place to ask for help in this =
language, please let me know.
Here is what I want to do, my comments in red:
CREATE OR REPLACE FUNCTION discover_nsu(integer) RETURNS integer as '
DECLARE
nsureturn integer;
nsumax integer;
caixaunitid alias for $1;
branchid integer;
BEGIN
branchid :=3D select t1.branch as result from caixa.caixaunit as t1, =
caixa.caixa as t2 where t2.caixaunit =3D (select caixaunit from =
caixa.caixaunit where t2.id =3D caixaunitid);
-- the select above will return to me a result of one row and one =
column, with a integer variable inside, and will assign its result to =
branchid.
nsumax :=3D select max(nsu) from caixa.view_transacao_agencia_nsu =
where branch =3D branchid;
-- here i'll use the var I discovered in the last select (branchid) and =
will do another select in a view (this view was previously created and =
works fine), and store the result of the query inside nsumax var.
IF (nsumax <=3D 0) OR (nsumax ISNULL) THEN
nsureturn:=3D0;
ELSE
nsureturn:=3Dnsumax + 1;
END IF;
RETURN nsureturn;
-- in the if-then-else above, i was just doing a simple test. If nsumax =
is equal or lower than 0, or nsumax is NULL, it'll assign 0 to the =
return var. Else, it'll get the max, add one, and assign the value to =
the return var, and finally, return it =3D)
END
' LANGUAGE 'plpgsql';
Okey, the function gets created fine b/c there are no sintax erros, the =
problem is when i try to execute:
database=3D> select discover_nsu(1);
ERROR: syntax error at or near "select" at character 9
QUERY: SELECT select t1.branch as result from caixa.caixaunit as t1, =
caixa.caixa as t2 where t2.caixaunit =3D (select caixaunit from =
caixa.cai
xaunit where t2.id =3D $1 )
CONTEXT: PL/pgSQL function "descobrir_nsu" line 7 at assignment
LINE 1: SELECT select t1.branch as result from caixa.caixaunit as t...
^
Well, the thing is: when I execute all the selects inside the stored =
procedure manually, they'll work, proving that there are no errors on =
the selects statements itself. I believe that the database cannot =
understand the type of the result, assuming that it's a row instead of a =
single record(??). I was looking at the PL/PgSQL reference manual and =
wasn't able to figure out a solution, so here I am .. can aonyone help =
me? Which type should I use to receive the return from the query? Are =
cast operations (for type conversions) supported in PL/PgSQL?
Thanks for all, please help!
Regards,
Igor
--
igor [at] providerst.com.br
------=_NextPart_000_002E_01C4C32B.9E910250
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2523" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT color=3D#000080 size=3D2>Hi Guys,</FONT></DIV>
<DIV><FONT color=3D#000080 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000080 size=3D2>I need some help on building the =
following
stored procedure, in PL/PgSQL. If this is not the right place to ask for =
help in
this language, please let me know.</FONT></DIV>
<DIV><FONT color=3D#000080 size=3D2>Here is what I want to do, my =
comments in
red:</FONT></DIV>
<DIV><FONT color=3D#000080 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000080 size=3D2>CREATE OR REPLACE FUNCTION =
discover_nsu(integer)
RETURNS integer as '<BR>DECLARE<BR> nsureturn
integer;<BR> nsumax integer;<BR> =
caixaunitid
alias for $1;<BR> branchid
integer;<BR>BEGIN<BR> branchid :=3D select t1.branch =
as result
from caixa.caixaunit as t1, caixa.caixa as t2 where t2.caixaunit =3D =
(select
caixaunit from caixa.caixaunit where t2.id =3D =
caixaunitid);</FONT></DIV>
<DIV><FONT color=3D#000080 size=3D2><FONT color=3D#ff0000>-- the select =
above will
return to me a result of one row and one column, with a integer variable =
inside,
and will assign its result to =
<STRONG>branchid</STRONG>.</FONT></FONT></DIV>
<DIV><FONT color=3D#000080 size=3D2><FONT color=3D#ff0000></FONT><FONT
color=3D#ff0000></FONT><FONT color=3D#ff0000></FONT><FONT
color=3D#ff0000></FONT><BR> nsumax :=3D select =
max(nsu) from
caixa.view_transacao_agencia_nsu where branch =3D branchid;</FONT></DIV>
<DIV><FONT color=3D#000080 size=3D2><FONT =
color=3D#ff0000>-- here i'll use
the var I discovered in the last select (branchid) and will do =
another
select in a view (this view was previously created and works fine), and =
store
the result of the query inside <STRONG>nsumax
</STRONG>var.<BR></FONT></FONT></DIV>
<DIV><FONT color=3D#000080 size=3D2> IF (nsumax =
<=3D 0) OR
(nsumax ISNULL) THEN</FONT></DIV>
<DIV><FONT color=3D#000080
size=3D2>
nsureturn:=3D0;<BR>
ELSE<BR> =
nsureturn:=3Dnsumax
+ 1;<BR> END IF;<BR> RETURN
nsureturn;</FONT></DIV>
<DIV><FONT color=3D#000080 size=3D2><FONT color=3D#ff0000>-- in the =
if-then-else
above, i was just doing a simple test. If nsumax is equal or lower than =
0, or
nsumax is NULL, it'll assign 0 to the return var. Else, it'll get the =
max, add
one, and assign the value to the return var, and finally, return it
=3D)</FONT></DIV>
<DIV> </DIV>
<DIV>END<BR>' LANGUAGE 'plpgsql';</DIV>
<DIV> </DIV>
<DIV>Okey, the function gets created fine b/c there are no sintax erros, =
the
problem is when i try to execute:</DIV>
<DIV> </DIV>
<DIV><FONT face=3DFixedsys>database=3D> select =
discover_nsu(1);<BR>ERROR:
syntax error at or near "select" at character 9<BR>QUERY: =
SELECT
select t1.branch as result from caixa.caixaunit as t1, caixa.caixa as t2 =
where
t2.caixaunit =3D (select caixaunit from caixa.cai<BR>xaunit where t2.id =
=3D $1
)<BR>CONTEXT: PL/pgSQL function "descobrir_nsu" line 7 at
assignment<BR>LINE 1: SELECT select t1.branch as result from
caixa.caixaunit as
t...<BR> =
;
^</FONT></DIV>
<DIV>Well, the thing is: when I execute all the selects inside the =
stored
procedure manually, they'll work, proving that there are no errors on =
the
selects statements itself. I believe that the database cannot understand =
the
type of the result, assuming that it's a row instead of a single =
record(??). I
was looking at the PL/PgSQL reference manual and wasn't able to figure =
out a
solution, so here I am .. can aonyone help me? Which type should I use =
to
receive the return from the query? Are cast operations (for type =
conversions)
supported in PL/PgSQL?</DIV>
<DIV> </DIV>
<DIV>Thanks for all, please help!</DIV>
<DIV> </DIV>
<DIV>Regards,</DIV>
<DIV>Igor<BR>--<BR><A
href=3D"mailto:igor [at] providerst.com.br">igor [at] providerst.com.b r</A></DIV>
<DIV></FONT> </DIV></BODY></HTML>
------=_NextPart_000_002E_01C4C32B.9E910250--
