Group by clause creating "ERROR: wrong record type supplied in RETURN NEXT" (version 8.1.11 -- grr...)

["Webb Sprague" <webb.sprague@xxxxxxxxx>]

Hi all,

If I try to execute a dynamic query inside a function with a group by
statement, returning a setof, I get a weird error.  It may be due to
the antiquated database version, but I would appreciate all the info I
can get (I tried looking in the PG bug tracker,  but ... hahaha).  If
it is as simple as upgrading, I will lean on the admin (I don't
control the box, or this wouldn't be an issue).  I will try to
duplicate on a new machine later this week.

First the pass-through function (takes a sql statement, tries to clean
it, executes it):

create or replace function mkn.query_table_data  (selectstring_p text)
        returns setof record as $_$
    DECLARE
        outputrec_v record;
        nasty_strings_re_v text;
        rowcnt int := 0;
    BEGIN
        -- build regex from table of nasty strings
        nasty_strings_re_v := (select
(array_to_string(array_accum(badword), '|')) from mkn.badwords);
        raise debug '%',  nasty_strings_re_v;
        if (selectstring_p ~* nasty_strings_re_v) then -- bad stuff in query
            raise exception 'Disallowed strings in query';
        else -- get the records and return them
            for outputrec_v in execute selectstring_p loop
                rowcnt := rowcnt + 1;
                return next outputrec_v;
            end loop;

            -- if no rows selected raise an exception (catch later)
            if rowcnt <= 0 then
                raise exception 'Zero rows returned';
            end if;
            insert into mkn.custom_queries_log (query, output_rows,
error_code, error_msg)
                                        values (selectstring_p,
rowcnt, NULL, NULL);
        end if;
    END;
$_$ LANGUAGE plpgsql;

-- Now a query that works OK being passed through this function

select * from mkn.query_table_data ('select p087001 as pop
                from datatable_00041 order by pop desc limit 10')
        as FOO (pop integer);
 pop
------
 3583
 3555
 3417
 3410
 3352
 3133
 3122
 3013
 2957
 2941
(10 rows)

-- Now a query that fails being passed through the function

select * from mkn.query_table_data ('select sum(p087001) as pop
        from datatable_00040 group by substr(geo_id, 13, 6) order by
pop desc limit 10')
    as FOO (pop integer);

ERROR:  wrong record type supplied in RETURN NEXT
CONTEXT:  PL/pgSQL function "query_table_data" line 15 at return next

-- Now, what happens if I run the failing inside query directly from psql

select sum(p087001) as pop from datatable_00041 group by
substr(geo_id, 13, 6) order by pop desc limit 10;
 pop
------
 7498
 7181
 7130
 7094
 6879
 6839
 6677
 6662
 6632
 6567
(10 rows)

-- Now, the version:

select version();
                                               version
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 8.1.11 on x86_64-redhat-linux-gnu, compiled by GCC gcc
(GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)
(1 row)

-- thanks to everyone for their help, yet again!

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general