Thread: bug? Drop column and SQL functions

bug? Drop column and SQL functions

From
Alvaro Herrera
Date:
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)


RES: bug? Drop column and SQL functions

From
"Thiago Fernandes Moesch"
Date:
  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



Re: RES: bug? Drop column and SQL functions

From
Rod Taylor
Date:
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.