returning error state and smallint parameter
Coniglio Sgabbiato
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/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
does anybody have a clue?
thank you in advance for help you might give
Laurenz Albe
The first problem is the harder one:
If you read the type conversion rules for functions on
http://www.postgresql.org/docs/current/static/typeconv-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
32046...@vodafone.it
[CUT]
> The first problem is the harder one:
>
> If you read the type conversion rules for functions onhttp://www.postgresql.org/docs/current/static/typeconv-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 yourtestfunctionis 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 fromsmallintto 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 yourfunctiondefinition, then thefunction
> could be called with both integer andsmallintarguments.
I followed your advice first, and used int instead of smallint. But
coming back to my question, my doubt was not the lack of cast from int
to smallint, it is obvious that an integer cannot be cast implicitly
to a smallint. My doubt was about the fact that a number passed to a
function is treated by default as integer, without checking first if
the string passed in the sql code is compatible sith the function
parameter input. In this case PG got the string "4" from sql code, put
it in some buffer as interger and only after tried to cast it to
smallint. IMHO PG should pick up the string "4" from the sql string
and check if is it compatible with the parameter type (in this case
smallint, and yeah, it is compatible) and then cast it. Do you get the
topic? I am sorry, my English is way bad so sometimes it is difficult
to me to express well some concepts.
>
> The second problem is simple.
>
> If you read the page you quoted above carefully, youl see
> that
>
> These variables are undefined outside exception handlers.
yes, I got it, sorry for my distraction in reading the documentation,
I was looking for a way to pass through the output of a stored to the
application caller code, I solved this way:
create or replace function blahblahblah(_param1 int, _param2 varchar,
etc) returns setof output_sp as $$
declare
raff int;
traff int;
r output_sp%rowtype;
begin
traff:=0;
-- statements...
get diagnostics raff:=row_count;
traff:=traff+raff;
-- other statements...
traff:=traff+raff;
r.output:=true;
r.records_affected:=traff;
if traff=0 then
r.output:=false;
r.error:='my custom error';
end if;
return next r;
return;
exception
when others then
r.error:=sqlerrm;
r.output:=false;
r.records_affected=0;
return next r;
return;
end;
$$ language plpgsql;
# \d output_sp
Table "public.output_sp"
Column | Type | Modifiers
------------------+---------+-----------
output | boolean |
error | text |
records_affected | bigint |
Laurenz Albe
[CUT]
>> If you read the type conversion rules for functions on
>> http://www.postgresql.org/docs/current/static/typeconv-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.
>
[...]
>
> I followed your advice first, and used int instead of smallint. But
> coming back to my question, my doubt was not the lack of cast from int
> to smallint, it is obvious that an integer cannot be cast implicitly
> to a smallint. My doubt was about the fact that a number passed to a
> function is treated by default as integer, without checking first if
> the string passed in the sql code is compatible sith the function
> parameter input. In this case PG got the string "4" from sql code, put
> it in some buffer as interger and only after tried to cast it to
> smallint. IMHO PG should pick up the string "4" from the sql string
> and check if is it compatible with the parameter type (in this case
> smallint, and yeah, it is compatible) and then cast it. Do you get the
> topic? I am sorry, my English is way bad so sometimes it is difficult
> to me to express well some concepts.
I understand you fine.
In your original query:
select test('r', 4, 's');
the first and the third have type "unknown", that is an internal
type assigned to all string constants.
As you say, the problem is that a numeric constant
without a decimal point is automatically assumed
to be an integer.
You can see this from the following example:
test=> CREATE TABLE test AS (SELECT 1 AS num_col, '1' AS char_col);
WARNING: column "char_col" has type "unknown"
DETAIL: Proceeding with relation creation anyway.
SELECT
test=> \d test
Table "laurenz.test"
Column | Type | Modifiers
----------+---------+-----------
num_col | integer |
char_col | unknown |
What you would like is an "unknown integer" type that
can be cast to all integers.
Do I get you right?
I don't know why there is no such thing... Maybe it
would get too complicated that way. Maybe nobody thought of it.
Does anybody else here have an idea?
Yours,
Laurenz Albe
PS: If you call your original function like that:
select test('r', '4', 's');
it would work, because then all three are "unknown".
Coniglio Sgabbiato
[CUT]
> In your original query:
> select test('r', 4, 's');
> the first and the third have type "unknown", that is an internal
> type assigned to all string constants.
>
> As you say, the problem is that a numeric constant
> without a decimal point is automatically assumed
> to be an integer.
>
> You can see this from the following example:
>
> test=> CREATE TABLE test AS (SELECT 1 AS num_col, '1' AS char_col);
> WARNING: column "char_col" has type "unknown"
> DETAIL: Proceeding with relation creation anyway.
> SELECT
> test=> \d test
> Table "laurenz.test"
> Column | Type | Modifiers
> ----------+---------+-----------
> num_col | integer |
> char_col | unknown |
>
> What you would like is an "unknown integer" type that
> can be cast to all integers.
> Do I get you right?
yes
>
> I don't know why there is no such thing... Maybe it
> would get too complicated that way. Maybe nobody thought of it.
> Does anybody else here have an idea?
>
> Yours,
> Laurenz Albe
>
> PS: If you call your original function like that:
> select test('r', '4', 's');
> it would work, because then all three are "unknown".
smart advice, it works good. The next time I'll quote numeric values too
in application code.