Thread: bug? Drop column and SQL functions

Someone showed me this simple example:

regression=# CREATE TABLE test (a TEXT, b TEXT);
CREATE TABLE
regression=# INSERT INTO test VALUES ('foo', 'bar');
INSERT 17145 1
regression=# CREATE FUNCTION foo() RETURNS SETOF test as 'SELECT * FROM test' LANGUAGE sql;
CREATE FUNCTION
regression=# SELECT * FROM foo(); a  |  b
-----+-----foo | bar
(1 registro)

regression=# ALTER TABLE test DROP COLUMN a;
ALTER TABLE
regression=# SELECT * FROM foo();
ERROR:  query-specified return row and actual function return row do not match

(note that I didn't "specify a return record" -- SETOF test should only
consider non-dropped columns ...)

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La virtud es el justo medio entre dos defectos" (Aristóteles)

  I have a comment on something like that to: Why - when creating a view
using explicit * on select - postgresql reads all the fields in the query
and especify them one by one on the view definition? Developers always have
to check every view after changing a table definition to be certain it does
not need to be recreated.
   That's a bug or a feature?
  Regards,  Thiago Moesch


-----Mensagem original-----
De: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org]Em nome de Alvaro Herrera
Enviada em: terça-feira, 28 de outubro de 2003 15:28
Para: Hackers
Assunto: [HACKERS] bug? Drop column and SQL functions


Someone showed me this simple example:

regression=# CREATE TABLE test (a TEXT, b TEXT);
CREATE TABLE
regression=# INSERT INTO test VALUES ('foo', 'bar');
INSERT 17145 1
regression=# CREATE FUNCTION foo() RETURNS SETOF test as 'SELECT * FROM
test' LANGUAGE sql;
CREATE FUNCTION
regression=# SELECT * FROM foo(); a  |  b
-----+-----foo | bar
(1 registro)

regression=# ALTER TABLE test DROP COLUMN a;
ALTER TABLE
regression=# SELECT * FROM foo();
ERROR:  query-specified return row and actual function return row do not
match

(note that I didn't "specify a return record" -- SETOF test should only
consider non-dropped columns ...)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La virtud es el justo medio entre dos defectos" (Aristóteles)

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003

On Wed, 2003-10-29 at 06:54, Thiago Fernandes Moesch wrote:
>    I have a comment on something like that to: Why - when creating a view
> using explicit * on select - postgresql reads all the fields in the query
> and especify them one by one on the view definition? Developers always have
> to check every view after changing a table definition to be certain it does
> not need to be recreated.

I would have thought dependency tracking added in 7.3 would tell you
when views cannot be updated to accommodate the table change.

Functions on the other hand do not have any dependency tracking.