Search code examples
postgresqlgreenplumhawq

Error : relation does not exist, on greenplum database

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.

Solution

  • The answer is function types.

    By official docs

    http://www.greenplumdba.com/greenplum-dba-faq/whatareimmutablestableandvolatilefunctionsingreenplum