Re: return value from SQL statement

On Tue, 25 Mar 2008 16:38:04 +0100
"Alain Roger" <raf(dot)news(at)gmail(dot)com> wrote:

> under pl/pgsql language i would like to return a function value.
> e.g :
> 0 is everything is completed
> -1 if searched data already exist into table
> -2 if insert into failed
> ...
>
> later i would like to use this returned value in my PHP (this i
> know how to do)
> is it clearer ?
> A.

some code snipplet

8<---8<---8<---8<---8<---8<---

create or replace function somefunc(param1 int, param2 int, out res
smallint) as

begin

...

end;

8<---8<---8<---8<---8<---8<---

res:=-1;

8<---8<---8<---8<---8<---8<---

GET DIAGNOSTICS _rowN = ROW_COUNT;

give a look to FOUND & Co.

http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

8<---8<---8<---8<---8<---8<---

$result=pg_query("select res from somefunc(....)");
$row=pg_fetch_array($result);
$res=$row['res'];

8<---8<---8<---8<---8<---8<---

give a look to

http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

8<---8<---8<---8<---8<---8<---

you may even be interested in setof & Co. and some clarifying
examples:

http://people.planetpostgresql.org/xzilla/index.php?/archives/149-out-parameter-sql-plpgsql-examples.html#extended

still your question look a bit too broad to give you one answer.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it