Bug in ALTER COLUMN/TYPE
I think we need to deny changing column types if a function is using the
table type as a return set.
test=# create table test (a int4);
CREATE TABLE
test=# create function test () returns setof test as 'select 1' language
sql;
CREATE FUNCTION
test=# alter table test alter a type bigint;
ALTER TABLE
test=# select * from test();
ERROR: return type mismatch in function declared to return test
DETAIL: Final SELECT returns integer instead of bigint at column 1.
CONTEXT: SQL function "test" during startup
Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
I think we need to deny changing column types if a function is using the
table type as a return set.
I disagree. There are many cases where it will work, and AFAIK none
in which you'll get worse than an error message. A couple of examples
where it works:
regression=# create table test (f1 int);
CREATE TABLE
regression=# insert into test values(42);
INSERT 155117 1
regression=# create function test () returns setof test as
regression-# 'select * from test' language sql;
CREATE FUNCTION
regression=# alter table test add column f2 text default 'foo';
ALTER TABLE
regression=# select * from test();
f1 | f2
----+-----
42 | foo
(1 row)
regression=# alter table test alter f1 type bigint;
ALTER TABLE
regression=# select * from test();
f1 | f2
----+-----
42 | foo
(1 row)
regression=#
regards, tom lane
On Tue, 2004-08-03 at 23:36, Christopher Kings-Lynne wrote:
I think we need to deny changing column types if a function is using the
table type as a return set.test=# create table test (a int4);
CREATE TABLE
test=# create function test () returns setof test as 'select 1' language
sql;
What we really need is dependencies within the function body and the
ability to clear the function cache (recompile).
--
rbt <at> sitesell <dot> com
What we really need is dependencies within the function body and the
ability to clear the function cache (recompile).
Can the new function validator function for pl/pgsql add dependencies
perhaps?
Chris
I disagree. There are many cases where it will work, and AFAIK none
in which you'll get worse than an error message. A couple of examples
where it works:
OK, fair enough.
Chris
Rod Taylor wrote:
On Tue, 2004-08-03 at 23:36, Christopher Kings-Lynne wrote:
I think we need to deny changing column types if a function is using the
table type as a return set.test=# create table test (a int4);
CREATE TABLE
test=# create function test () returns setof test as 'select 1' language
sql;What we really need is dependencies within the function body and the
ability to clear the function cache (recompile).
I notice we didn't have this on the TODO --- added:
* Track dependencies in function bodies and recompile/invalidate
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073