BUG #5240: Stable Functions that return a table type with a dropped column fail - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5240
Logged by:          David Gardner
Email address:      dgardner@creatureshop.com
PostgreSQL version: 8.4.1
Operating system:   Debian Linux, amd64 2.6.30
Description:        Stable Functions that return a table type with a dropped
column fail
Details:

SELECT foo(); works while SELECT * FROM foo(); fails.
However redefining the function as volatile fixes the issue. Possibly
related to BUG #4907.

-----------------------
dgardner@cssun32 ~:$ psql -h localhost -d hdpsdb_baseline
Password:
psql (8.4.1)
Type "help" for help.

hdpsdb_baseline=# SELECT * FROM version();
                                               version

----------------------------------------------------------------------------
--------------------------
 PostgreSQL 8.4.1 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real
(Debian 4.3.4-2) 4.3.4, 64-bit
(1 row)

hdpsdb_baseline=# SELECT a.attnum, a.attname AS field,
hdpsdb_baseline-#        a.attlen AS length, a.atttypmod AS length_var,
hdpsdb_baseline-#        a.attnotnull AS not_null, a.atthasdef as
has_default
hdpsdb_baseline-#   FROM pg_class c, pg_attribute a
hdpsdb_baseline-#  WHERE c.relname = 'users'
hdpsdb_baseline-#    AND a.attnum > 0
hdpsdb_baseline-#    AND a.attrelid = c.oid
hdpsdb_baseline-#  ORDER BY a.attnum;
 attnum |            field             | length | length_var | not_null |
has_default
--------+------------------------------+--------+------------+----------+---
----------
      1 | userid                       |     -1 |        259 | t        | f
      2 | name                         |     -1 |        259 | t        | f
      3 | email                        |     -1 |         -1 | f        | f
      4 | ........pg.dropped.4........ |      1 |         -1 | f        | f
      5 | enabled                      |      1 |         -1 | t        | t
(5 rows)

hdpsdb_baseline=# CREATE OR REPLACE FUNCTION get_users()
hdpsdb_baseline-#   RETURNS SETOF users AS
hdpsdb_baseline-# $BODY$
hdpsdb_baseline$# SELECT users.*
hdpsdb_baseline$# FROM
hdpsdb_baseline$# users
hdpsdb_baseline$# ORDER BY users.userid LIMIT 1;
hdpsdb_baseline$# $BODY$
hdpsdb_baseline-#   LANGUAGE 'sql' STABLE;
CREATE FUNCTION
hdpsdb_baseline=# ALTER FUNCTION get_users() OWNER TO dgardner;
ALTER FUNCTION
hdpsdb_baseline=# GRANT EXECUTE ON FUNCTION get_users() TO public;
GRANT
hdpsdb_baseline=# GRANT EXECUTE ON FUNCTION get_users() TO dgardner;
GRANT
hdpsdb_baseline=#
hdpsdb_baseline=# SELECT * FROM get_users();
ERROR:  invalid attribute number 5
hdpsdb_baseline=# SELECT get_users();
               get_users
---------------------------------------
 (aa_test_user,test,test@here.there,t)
(1 row)

hdpsdb_baseline=# CREATE OR REPLACE FUNCTION get_users()
hdpsdb_baseline-#   RETURNS SETOF users AS
hdpsdb_baseline-# $BODY$
hdpsdb_baseline$# SELECT users.*
hdpsdb_baseline$# FROM
hdpsdb_baseline$# users
hdpsdb_baseline$# ORDER BY users.userid LIMIT 1;
hdpsdb_baseline$# $BODY$
hdpsdb_baseline-#   LANGUAGE 'sql' VOLATILE;
CREATE FUNCTION
hdpsdb_baseline=# ALTER FUNCTION get_users() OWNER TO dgardner;
ALTER FUNCTION
hdpsdb_baseline=# GRANT EXECUTE ON FUNCTION get_users() TO public;
GRANT
hdpsdb_baseline=# GRANT EXECUTE ON FUNCTION get_users() TO dgardner;
GRANT
hdpsdb_baseline=# SELECT * FROM get_users();
    userid    | name |      email      | enabled
--------------+------+-----------------+---------
 aa_test_user | test | test@here.there | t
(1 row)

hdpsdb_baseline=#