X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2016A3A3B07 for ; Fri, 5 Nov 2004 14:30:59 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 26528-07 for ; Fri, 5 Nov 2004 14:30:53 +0000 (GMT) Received: from providerst.com.br (unknown [200.253.138.61]) by svr1.postgresql.org (Postfix) with ESMTP id 6C1313A3BEB for ; Fri, 5 Nov 2004 14:30:48 +0000 (GMT) Brma: igor [192.168.10.2] Received: from igor (igor [192.168.10.2]) by providerst.com.br (8.11.6/8.11.6) with SMTP id iA5DUgv19517 for ; Fri, 5 Nov 2004 11:30:42 -0200 Message-ID: <003101c4c344$c741fec0$020aa8c0@igor> From: "Igor Maciel Macaubas" To: Subject: Help in stored procedure Date: Fri, 5 Nov 2004 11:35:58 -0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002E_01C4C32B.9E910250" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.7 tagged_above=0.0 required=5.0 tests=HTML_20_30, HTML_FONTCOLOR_BLUE, HTML_FONTCOLOR_RED, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200411/42 X-Sequence-Number: 19361 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@providerst.com.br ------=_NextPart_000_002E_01C4C32B.9E910250 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi Guys,
 
I need some help on building the = following=20 stored procedure, in PL/PgSQL. If this is not the right place to ask for = help in=20 this language, please let me know.
Here is what I want to do, my = comments in=20 red:
 
CREATE OR REPLACE FUNCTION = discover_nsu(integer)=20 RETURNS integer as '
DECLARE
    nsureturn=20 integer;
    nsumax integer;
    = caixaunitid=20 alias for $1;
    branchid=20 integer;
BEGIN
    branchid :=3D select t1.branch = as result=20 from caixa.caixaunit as t1, caixa.caixa as t2 where t2.caixaunit =3D = (select=20 caixaunit from caixa.caixaunit where t2.id =3D = caixaunitid);
-- the select = above will=20 return to me a result of one row and one column, with a integer variable = inside,=20 and will assign its result to = branchid.

    nsumax :=3D select = max(nsu) from=20 caixa.view_transacao_agencia_nsu where branch =3D branchid;
-- here i'll use=20 the var I discovered in the last select (branchid) and will do = another=20 select in a view (this view was previously created and works fine), and = store=20 the result of the query inside nsumax=20 var.
    IF (nsumax = <=3D 0) OR=20 (nsumax ISNULL) THEN
         =20 nsureturn:=3D0;
   =20 ELSE
          = nsureturn:=3Dnsumax=20 + 1;
    END IF;
    RETURN=20 nsureturn;
-- in the = if-then-else=20 above, i was just doing a simple test. If nsumax is equal or lower than = 0, or=20 nsumax is NULL, it'll assign 0 to the return var. Else, it'll get the = max, add=20 one, and assign the value to the return var, and finally, return it=20 =3D)
 
END
' LANGUAGE 'plpgsql';
 
Okey, the function gets created fine b/c there are no sintax erros, = the=20 problem is when i try to execute:
 
database=3D> select = discover_nsu(1);
ERROR: =20 syntax error at or near "select" at character 9
QUERY:  = SELECT =20 select t1.branch as result from caixa.caixaunit as t1, caixa.caixa as t2 = where=20 t2.caixaunit =3D (select caixaunit from caixa.cai
xaunit where t2.id = =3D  $1=20 )
CONTEXT:  PL/pgSQL function "descobrir_nsu" line 7 at=20 assignment
LINE 1: SELECT  select t1.branch as result from=20 caixa.caixaunit as=20 t...
           = ;    =20 ^
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 = the=20 selects statements itself. I believe that the database cannot understand = the=20 type of the result, assuming that it's a row instead of a single = record(??). I=20 was looking at the PL/PgSQL reference manual and wasn't able to figure = out a=20 solution, so here I am .. can aonyone help me? Which type should I use = to=20 receive the return from the query? Are cast operations (for type = conversions)=20 supported in PL/PgSQL?
 
Thanks for all, please help!
 
Regards,
Igor
--
igor@providerst.com.br
 
------=_NextPart_000_002E_01C4C32B.9E910250--