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.

If you need an answer to this, you could help it along by providing the complete function definition, the table definition, the schema for each object and what you get for SHOW search_path. Also: did you run all tests in the same session with the same settings? BTW, "Postgre" is not an accepted name for Postgres. — Erwin Brandstetter
@Cixy: Don't know if is your case, but are you creating "my_table1" as temporary table? AFAIK, Postgres 8.2 has a bug on it that raises when you try to select a temp table created by a function and selected by another one... the workaround is to "select" the temp table using "execute" statement. check this out: stackoverflow.com/questions/19353438/… — Christian
@Christian B. Almeida. No, not a temporary table. Just normal — Clxy
The 'exists' operator doesn't work with greenplum using 8.2 postgres — precose

Gregor Raýman Gregor Raýman · Accepted Answer · 2015-04-14T12:42:19

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.

Error : relation does not exist, on greenplum database

3 Answers