Help in stored procedure

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



charset=3Diso-8859-1">

Re: Help in stored procedure

I think you want to be using SELECT INTO rather than assignment for
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
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
> 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:
> =A0
> CREATE OR REPLACE FUNCTION discover_nsu(integer) RETURNS integer as '
> DECLARE
>   =A0 nsureturn integer;
>   =A0 nsumax integer;
>   =A0 caixaunitid alias for $1;
>   =A0 branchid integer;
> BEGIN
>   =A0 branchid :=3D select t1.branch as result from caixa.caixaunit a=
s 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=20=
=20
> branchid.
>
>   =A0 nsumax :=3D select max(nsu) from caixa.view_transacao_agencia_n=
su
> where branch =3D branchid;
> --=A0here=A0i'll use the var I discovered in the last=A0select (branchid)=

> and will do another select in a view (this view was previously created=20=
=20
> and works fine), and store the result of the query inside nsumax var.
>   =A0 IF (nsumax <=3D 0) OR (nsumax ISNULL) THEN
>         =A0 nsureturn:=3D0;
>   =A0 ELSE
>         =A0 nsureturn:=3Dnsumax + 1;
>   =A0 END IF;
>   =A0 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=20=
=20
> the return var. Else, it'll get the max, add one, and assign the value=20=
=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,
> 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=
,
> caixa.caixa as t2 where t2.caixaunit =3D (select caixaunit from
> caixa.cai
> xaunit where t2.id =  $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 ^
> Well, the thing is: when I execute all the selects inside the stored=20=
=20
> procedure manually, they'll work, proving that there are no errors on=20=
=20
> the selects statements itself. I believe that the database cannot
> understand the type of the result, assuming that it's a row instead of=20=
=20
> 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=20=
=20
> help me? Which type should I use to receive the return from the query?=20=
=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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)