Help in stored procedure - Mailing list pgsql-sql
| From | Igor Maciel Macaubas |
|---|---|
| Subject | Help in stored procedure |
| Date | |
| Msg-id | 003101c4c344$c741fec0$020aa8c0@igor Whole thread Raw |
| Responses |
Re: Help in stored procedure
|
| List | pgsql-sql |
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 := select t1.branch as result from caixa.caixaunit as t1, caixa.caixa as t2 where t2.caixaunit = (select caixaunit from caixa.caixaunit where t2.id = caixaunitid);
DECLARE
nsureturn integer;
nsumax integer;
caixaunitid alias for $1;
branchid integer;
BEGIN
branchid := select t1.branch as result from caixa.caixaunit as t1, caixa.caixa as t2 where t2.caixaunit = (select caixaunit from caixa.caixaunit where t2.id = 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 := select max(nsu) from caixa.view_transacao_agencia_nsu where branch = 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 <= 0) OR (nsumax ISNULL) THEN
nsureturn:=0;
ELSE
nsureturn:=nsumax + 1;
END IF;
RETURN nsureturn;
ELSE
nsureturn:=nsumax + 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 =)
END
' LANGUAGE 'plpgsql';
' LANGUAGE 'plpgsql';
Okey, the function gets created fine b/c there are no sintax erros, the problem is when i try to execute:
database=> 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 = (select caixaunit from caixa.cai
xaunit where t2.id = $1 )
CONTEXT: PL/pgSQL function "descobrir_nsu" line 7 at assignment
LINE 1: SELECT select t1.branch as result from caixa.caixaunit as t...
^
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 = (select caixaunit from caixa.cai
xaunit where t2.id = $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,