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


Wed, 04 Aug 2004 11:36:22 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Bug in ALTER COLUMN/TYPE
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

Wed, 04 Aug 2004 09:54:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug in ALTER COLUMN/TYPE 
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


Wed, 04 Aug 2004 10:14:21 -0400
Rod Taylor <rbt@sitesell.com>
Re: Bug in ALTER COLUMN/TYPE
> 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


Thu, 05 Aug 2004 09:17:02 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Bug in ALTER COLUMN/TYPE
> 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


Thu, 05 Aug 2004 09:17:19 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Bug in ALTER COLUMN/TYPE
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

Fri, 6 Aug 2004 11:24:08 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Bug in ALTER COLUMN/TYPE