Error : relation does not exist, on greenplum database

Question

I'm working on PostgreSQL 8.2.15 (Greenplum database 4.2.0 build 1)(HAWQ 1.2.1.0 build 10335).

I wrote a function like

create or replace function my_function ( 
...
    select exists(select 1 from my_table1 where condition) into result;

I tested it like

select my_function(params);

It can totally work!

Here is the problem, if I call the function like

select my_function(params) from my_table2;

Postgres told me you're wrong !

ERROR: relation "my_table1" does not exist (segXX sliceX xx.xx.xx:40003 pid=570406)

• Those tables and function are in same schema.
• I can access them.
• Both names are lower case.

So, help me please.

What I tried

• move those tables from my_schema to public
• move function to public
• add schema prefix like my_schema.my_table1.

---

Edited by 2015/04/19

Postgre -> Postgres

And I tried it like

select my_function(params) from pg_stat_activity;

It's OK.

If edit that function like

create or replace function my_function ( 
...
    select true into result;

It can work at any case.

Answer 1

In Postgresql the functions usually run in the current search path of the session, so the problem might be that the schema my_schema is not in the current search path, when you run the function.

You can solve the problem by changing the declaration of your function to:

create or replace function my_function (...) ... as $$
 ....
$$ language plpgsql set search_path from current;

(I am not sure whether this works in the version 8)

If the clause set search_path from current does not work in 8.2, here an example how to set the search path temporarily within the function.