Hi all, I have _two_ problems, please consider the following example
function:

drop function if exists test(varchar, smallint, varchar);
create or replace function test(param1 varchar, param2 smallint, param3
varchar, out state text, out errm text) as $$
declare
begin
--statements
state:=sqlstate;
errm:=sqlerrm;
return;
exception
when others then
state:=sqlstate;
errm:=sqlerrm;
return;
end;
$$ language plpgsql;

so i get:

db_atm=# select test('r', 4, 's');
ERROR: function test(unknown, integer, unknown) does not exist
LINE 1: select test('r', 4, 's');
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
db_atm=# select test('r', cast(4 as smallint), 's');
test
----------------------------------------------
(42703,"column ""sqlstate"" does not exist")
(1 row)

the first problem is that I have to cast the input parameter even if it
is compatible with the declared type, the second one is that postgres
does not recognize the variables sqlstate and sqlerrm as reported in:
http://www.postgresql.org/docs/8.3/s...ERROR-TRAPPING

does anybody have a clue?

thank you in advance for help you might give

Coniglio Sgabbiato wrote:
The first problem is the harder one:

If you read the type conversion rules for functions on
http://www.postgresql.org/docs/curre...conv-func.html
you will see in rule 4.a. that PostgreSQL will only consider
functions where every argument can be *implicitly* cast to
the respective parameter type.

Now let's look at the cast from "integer" (= int4) to
"smallint" (= int2):

SELECT c.castcontext
FROM pg_catalog.pg_type s
JOIN pg_catalog.pg_cast c ON (s.oid = c.castsource)
JOIN pg_catalog.pg_type t ON (c.casttarget = t.oid)
WHERE s.typname = 'int4' AND t.typname = 'int2';

castcontext
-------------
a
(1 row)

So you see that the cast is an "assignment cast" and not
an "implicit cast", which is the reason why your test
function is not considered.

You can mess with PostgreSQL's type cast system by making that
cast implicit, but it might have all kinds of undesirable side
effects and I would not recommend that. I guess there is a
reason why the cast from smallint to integer is implicit and
the cast in the other direction is not.

Maybe the best solution would be to use "integer" instead of
"smallint" in your function definition, then the function
could be called with both integer and smallint arguments.

The second problem is simple.

If you read the page you quoted above carefully, youl see
that

These variables are undefined outside exception handlers.

So what happens is that your statements (or the comment
line "--statements") are executed without error, but the
assignment that follows immediately after causes the observed
error because "sqlstate" is not defined outside of the
exception handler.

This error then causes execution to continue in the "WHEN OTHERS"
exception handler, where sqlstate and sqlerrm *are* defined and
the error is dutifully reported.

To report something meaningful, you could set "state" to 0
and "errm" to NULL or '' *before* you execute the statements and
get rid of the two bad assignments afterwards.

Yours,
Laurenz Albe