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.