Thread: Changing a column's type

Hi All,
    I was using 7.2 and my sequences had a column type of int. I didn't
think much of this until I recently built the RPMs for 7.3 and
"upgraded". i.e. dumped out and imported again. I not that it had all
gone okay until I called a function that was working in 7.2 that is now
broken in 7.3

Reason being is that the column type for the sequence values is now
bigInt and of course I foolishly have a number of functions defined as
taking an INT. So of course the function bails because it can't find a
definition for a function matching bigint.

Anyway, to cut a long story short I was wondering if it is possible to
change the existing type of the sequence columns back to Int or recreate
the sequence with a type of int4.....

And yes, I should probably fix my functions to work with the bigInt
type, but I'm also curious to know if this is possible.

Thanks.
--
Hadley Willan > Systems Development > Deeper Design Limited. +64(7)377-3328
hadley.willan@deeper.co.nz > www.deeperdesign.com > +64(21)-28-41-463
Level 1, 4 Tamamutu St, PO Box 90, TAUPO 2730, New Zealand.

Hadley Willan <hadley.willan@deeper.co.nz> writes:
> Reason being is that the column type for the sequence values is now
> bigInt and of course I foolishly have a number of functions defined as
> taking an INT.

I don't follow.  You're executing functions on the columns of a sequence
object?  Why?

            regards, tom lane

Sorry perhaps I wasn't clear.

FUNCTION fn_create_new_item()
    _seq RECORD;
begin
    SELECT INTO _seq NEXTVAL(''source_sequence'');
    ..do stuff, insert ....
    PERFORM fn_b( _seq.next_value );
end;

FUNCTION fn_do_stuff_with_new_item_id( INT )
.....


Call to fn_b breaks because _seq.next_value is of type BIGINT.





On Tue, 2002-12-17 at 12:59, Tom Lane wrote:
> Hadley Willan <hadley.willan@deeper.co.nz> writes:
> > Reason being is that the column type for the sequence values is now
> > bigInt and of course I foolishly have a number of functions defined as
> > taking an INT.
>
> I don't follow.  You're executing functions on the columns of a sequence
> object?  Why?
>
>             regards, tom lane
--
Hadley Willan > Systems Development > Deeper Design Limited. +64(7)377-3328
hadley.willan@deeper.co.nz > www.deeperdesign.com > +64(21)-28-41-463
Level 1, 4 Tamamutu St, PO Box 90, TAUPO 2730, New Zealand.

Hadley Willan <hadley.willan@deeper.co.nz> writes:
> FUNCTION fn_create_new_item()
>     _seq RECORD;
> begin
>     SELECT INTO _seq NEXTVAL(''source_sequence'');
>     ..do stuff, insert ....
>     PERFORM fn_b( _seq.next_value );
> end;

> Call to fn_b breaks because _seq.next_value is of type BIGINT.

Oh, I see.  It seems like a rather random coding technique: why'd you
not declare _seq as type int?  If you don't want to change that, you
could insert an explicit cast in the PERFORM, too:

    PERFORM fn_b( _seq.next_value::int );

There's been some recent talk of allowing fn_b() with an int8 argument
to be resolved as fn_b(int4) --- with a runtime down-conversion --- if
there are no other candidates for fn_b().  But no one's put forward a
convincing argument for that as yet.  It might just add confusion.

            regards, tom lane

I chose not to declare _seq as type Int because I wanted access to the
entire record and it's columns. The function is a bit longer than just,
really ;-)

Anyway it's more a case of an explicit cast will fix it for now, thanks
for that.

As for the runtime down-conversion. I could see it being handy, but a
little dangerous. I could see it throwing an exception of course when
the down-conversion was impossible, i.e. bigint value exceeds int. So as
you say why bother.

Hadley


On Tue, 2002-12-17 at 13:12, Tom Lane wrote:
> Hadley Willan <hadley.willan@deeper.co.nz> writes:
> > FUNCTION fn_create_new_item()
> >     _seq RECORD;
> > begin
> >     SELECT INTO _seq NEXTVAL(''source_sequence'');
> >     ..do stuff, insert ....
> >     PERFORM fn_b( _seq.next_value );
> > end;
>
> > Call to fn_b breaks because _seq.next_value is of type BIGINT.
>
> Oh, I see.  It seems like a rather random coding technique: why'd you
> not declare _seq as type int?  If you don't want to change that, you
> could insert an explicit cast in the PERFORM, too:
>
>     PERFORM fn_b( _seq.next_value::int );
>
> There's been some recent talk of allowing fn_b() with an int8 argument
> to be resolved as fn_b(int4) --- with a runtime down-conversion --- if
> there are no other candidates for fn_b().  But no one's put forward a
> convincing argument for that as yet.  It might just add confusion.
>
>             regards, tom lane
--
Hadley Willan > Systems Development > Deeper Design Limited. +64(7)377-3328
hadley.willan@deeper.co.nz > www.deeperdesign.com > +64(21)-28-41-463
Level 1, 4 Tamamutu St, PO Box 90, TAUPO 2730, New Zealand.