Re: BUG #6020: Wrong data type returned after CAST in FROM - Mailing list pgsql-bugs

Hi there,

I've no idea if this is the proper way to append information about an
existing bug report, please advise if not.

The bug #6020 seems to have bit me, too, this kind of a query fails under
PostgreSQL 9.0.4:

SELECT * FROM acl_get_list('news', 258, 0);
ERROR:  wrong record type supplied in RETURN NEXT
DETAIL:  Returned type bpchar does not match expected type character(4) in column 3.
CONTEXT:  PL/pgSQL function "acl_get_list" line 9 at RETURN NEXT

Where the code is:

CREATE TYPE acl_list AS (
  ugid integer,
  isuser boolean,
  acl_id CHAR(4)
[...]
);

CREATE OR REPLACE FUNCTION acl_get_list(CHAR(4), INTEGER, INTEGER) RETURNS SETOF acl_list AS '
DECLARE
    p_acl_id          ALIAS FOR $1;
    p_acl_object_id   ALIAS FOR $2;
    p_lev             ALIAS FOR $3;
    rec               RECORD;
    rec1              RECORD;
BEGIN
[...]
    FOR rec IN SELECT * FROM acl_inherits WHERE acl_id = p_acl_id AND object_id = p_acl_object_id LOOP
        FOR rec1 IN SELECT * FROM acl_get_list(rec.parent_acl_id, rec.parent_object_id, p_lev + 1) LOOP
            RETURN NEXT rec1;
        END LOOP;
    END LOOP;
[...]

This worked just fine in 8.1, 8.3, 8.4, and doesn't seem wrong.

(Coupled with http://bugs.debian.org/630569, this upgrade cycle
has not been happy at all for me... :()

--
     2. That which causes joy or happiness.