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?

What database version are you on? I did a quick check on 8.2, 9.2, and 9.3 and none of them gave me that error (and a search through the code didn't find that error string). Also, you should be using quote_ident() or format() - if any of your column names have weird characters you could be passing a screwy query to execute. — yieldsfalsehood
The language name is a SQL identifier and should not be put into single quotes. You should use plpgsql instead. — a_horse_with_no_name
I am on PostgreSQL 8.2.15. I'm not sure why this error is not repeatable in those versions. I get the same error if I leave the quotes off of the language name (plpgsql). — user3062144

Wes Reing Wes Reing · Accepted Answer · 2014-11-04T22:18:12

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.

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

2 Answers