Thread: Group by clause creating "ERROR: wrong record type supplied in RETURN NEXT" (version 8.1.11 -- grr...)
Group by clause creating "ERROR: wrong record type supplied in RETURN NEXT" (version 8.1.11 -- grr...)
From
"Webb Sprague"
Date:
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!
Re: Group by clause creating "ERROR: wrong record type supplied in RETURN NEXT" (version 8.1.11 -- grr...)
From
Klint Gore
Date:
Webb Sprague wrote: > 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 > sum(int) returns bigint klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: kgore4@une.edu.au