From: Thomas F.O'Connell <tfo@sitening.com>
To: Igor Maciel Macaubas <igor@providerst.com.br>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help in stored procedure
Date: Fri, 5 Nov 2004 09:13:31 -0600
Message-ID: <40FE6F0D-2F3D-11D9-AA1B-000D93AE0944@sitening.com> (raw)
In-Reply-To: <003101c4c344$c741fec0$020aa8c0@igor>
References: <003101c4c344$c741fec0$020aa8c0@igor>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tfo@sitening.com, igor@providerst.com.br
Subject: Re: Help in stored procedure
In-Reply-To: <40FE6F0D-2F3D-11D9-AA1B-000D93AE0944@sitening.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox