PostgreSQL Error: function cannot execute on segment because it accesses relation "..."

Question

I am writing a simple plpgsql function to test an idea I have to use the information_schema.columns table to dynamically run metrics on various tables. The function works fine, but when I use info in the information_schema table to generate the table name to pass to my function, I get the error message in the title:

ERROR:  function cannot execute on segment because it accesses relation "my_table"

Here is the simple function (a proof-of-principle):

create or replace function count_rows(table_name text, column_name text)
returns bigint as $$
declare
    n bigint;
BEGIN
    execute 'select count(*) from (select ' || column_name || ' from ' || table_name || ') as t' into n;
    return n;
END;
$$ language 'plpgsql';

This query (and therefore the function) works fine:

select * from count_rows('my_table','my_column');  -- works correctly!

But this query using inputs from the information_schema.columns table fails with the error above:

select table_name, column_name, count_rows(table_name, column_name) as num_rows
from information_schema.columns where table_name = 'my_table'; -- doesnt work

What does this error message mean? Why can't it query the table listed in information_schema in this way?

Answer 1

use quote_ident(tablename) & quote_ident(columnname) instead of direct columnname and tablename, you should require access to all the tables

Answer 2

It looks like you are probably using Greenplum. If so the problem is that functions cannot access tables.

If you have this problem you have to either rewrite your function as a view, or hardcode the values returned in the table select in the function. In this case it doesn't make sense to hardcode the results so you would need to see if you can make a view work.