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
--
[EMAIL PROTECTED]
�
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
