Re: Smallint - Integer Casting Problems in Plpgsql

Hi, i'm upgrading our database from postgresql 7.2.3 to 7.4.2.
The import went fine but i have some casting problems with plpgsql
functions.

I've create a test function with this code:

create function test(varchar,smallint,integer) returns integer as '
declare
a alias for $1;
b alias for $2;
c alias for $3;
begin
raise notice ''test'';
return 1;
end;
' language 'plpgsql';

The command:
select test('aaa',1,1);
gives me the following error:
ERROR: function test("unknown", integer, integer) does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.

Obviously, with the command:
select test('aaa',1::smallint,1);

the result is 1.

Thank you in advance for your help,

--
Doct. Eng. Denis Gasparin: denis(at)edistar(dot)com
---------------------------
Programmer & System Administrator - Edistar srl

On Wednesday 17 March 2004 15:54, Denis Gasparin wrote:
> Hi, i'm upgrading our database from postgresql 7.2.3 to 7.4.2.
> The import went fine but i have some casting problems with plpgsql
> functions.
>
> I've create a test function with this code:
>
> create function test(varchar,smallint,integer) returns integer as '

> select test('aaa',1,1);
> gives me the following error:
> ERROR: function test("unknown", integer, integer) does not exist

Easiest solution is to just define the function as accepting integer rather
than smallint.
I believe the typeing will be smarter in 7.5 but don't know if it will affect
this situation.

--
Richard Huxton
Archonet Ltd

Hi Richard.
Thank you for your reply. I rewrote the store procedure to accept
integer instead of smallint.

What i don't understand is why the casting is working in 7.2.3. What
has been changed from that?

Thank you,

--
Doct. Eng. Denis Gasparin: denis(at)edistar(dot)com
---------------------------
Programmer & System Administrator - Edistar srl

Richard Huxton wrote:

>On Wednesday 17 March 2004 15:54, Denis Gasparin wrote:
>
>
>>Hi, i'm upgrading our database from postgresql 7.2.3 to 7.4.2.
>>The import went fine but i have some casting problems with plpgsql
>>functions.
>>
>>I've create a test function with this code:
>>
>>create function test(varchar,smallint,integer) returns integer as '
>>
>>
>
>
>
>>select test('aaa',1,1);
>>gives me the following error:
>>ERROR: function test("unknown", integer, integer) does not exist
>>
>>
>
>Easiest solution is to just define the function as accepting integer rather
>than smallint.
>I believe the typeing will be smarter in 7.5 but don't know if it will affect
>this situation.
>
>
>

On Thursday 18 March 2004 07:52, Denis Gasparin wrote:
> Hi Richard.
> Thank you for your reply. I rewrote the store procedure to accept
> integer instead of smallint.
>
> What i don't understand is why the casting is working in 7.2.3. What
> has been changed from that?

The type system was tightened up. It's been made smarter for 7.5, but I'm not
sure it deals with this.

--
Richard Huxton
Archonet Ltd

>The type system was tightened up. It's been made smarter for 7.5, but I'm not
>sure it deals with this.
>
>
>
I suspected it was so. I hope that in 7.5 the problem will be solved
because i think it is more a bug than a feature and many people will be
asking for it...

I hope Tom Lane and the others Postgresql developers will be reading this.

Thank you,

--
Doct. Eng. Denis Gasparin: denis(at)edistar(dot)com
---------------------------
Programmer & System Administrator - Edistar srl

--- Denis Gasparin <denis(at)edistar(dot)com> wrote:
>
> >The type system was tightened up. It's been made
> smarter for 7.5, but I'm not
> >sure it deals with this.
> >
> >
> >
> I suspected it was so. I hope that in 7.5 the
> problem will be solved
> because i think it is more a bug than a feature and
> many people will be
> asking for it...
>
> I hope Tom Lane and the others Postgresql developers
> will be reading this.

If you search the mailing list archives you will find
plenty of discussions on this issue, some quite
recent. If you still have issues or questions to
raise after reading those discussions, then you will
certainly not be wasting the developers' time by
posting them.

>
> Thank you,
>
> --
> Doct. Eng. Denis Gasparin: denis(at)edistar(dot)com
> ---------------------------
> Programmer & System Administrator - Edistar srl
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to
> majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list
cleanly

__________________________________
Do you Yahoo!?
Yahoo! Mail - More reliable, more storage, less spam
http://mail.yahoo.com

On Thu, 2004-03-18 at 10:54, Jeff Eckermann wrote:
> --- Denis Gasparin <denis(at)edistar(dot)com> wrote:
> >
> > >The type system was tightened up. It's been made
> > smarter for 7.5, but I'm not
> > >sure it deals with this.
> > >
> > I suspected it was so. I hope that in 7.5 the
> > problem will be solved
> > because i think it is more a bug than a feature and
> > many people will be
> > asking for it...
> >
> > I hope Tom Lane and the others Postgresql developers
> > will be reading this.
>
> If you search the mailing list archives you will find
> plenty of discussions on this issue, some quite
> recent. If you still have issues or questions to
> raise after reading those discussions, then you will
> certainly not be wasting the developers' time by
> posting them.
>

I'll add that on the 7.5 install I tested this on (which admittedly is a
bit old), it was still "busted", so if you want to see this changed
you'll need to be a squeaky wheel.

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL