Re: Help in stored procedure
От | Thomas F.O'Connell |
---|---|
Тема | Re: Help in stored procedure |
Дата | |
Msg-id | 40FE6F0D-2F3D-11D9-AA1B-000D93AE0944@sitening.com обсуждение исходный текст |
Ответ на | Help in stored procedure ("Igor Maciel Macaubas" <igor@providerst.com.br>) |
Список | pgsql-sql |
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- statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT http://www.postgresql.org/docs/7.4/static/plpgsql- 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 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); > -- 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; > -- 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'; > > 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... > ^ > 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 >
В списке pgsql-sql по дате отправления: