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!