8

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.

Greg Chase
  • 173
  • 8
Clxy
  • 505
  • 1
  • 5
  • 13
  • 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](https://wiki.postgresql.org/wiki/Identity_Guidelines). – Erwin Brandstetter Apr 17 '15 at 02:57
  • @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: http://stackoverflow.com/questions/19353438/postgres-doesnt-recognize-temp-table-in-function – Christian Apr 17 '15 at 13:10
  • @Christian B. Almeida. No, not a temporary table. Just normal – Clxy May 18 '15 at 04:00
  • The 'exists' operator doesn't work with greenplum using 8.2 postgres – precose May 18 '15 at 20:10

3 Answers3

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.

Gregor Raýman
  • 3,051
  • 13
  • 19
  • Thats clearly not it, since `tables and function are in same schema`. If `my_table2` can be found, the same is true for `my_table1`. – Erwin Brandstetter Apr 15 '15 at 17:54
  • It would help to have even a minimal example of compilable code that exhibits the problem, so that we could reproduce it. – Gregor Raýman Apr 15 '15 at 18:57
  • Sorry to answer so late. We moved to different way. I tested your answer today. It can work (using the method in that link you gived). Thank you so much!!! Try to add bounty, but failed. I will try it again later. – Clxy May 14 '15 at 07:45
  • Sorry, I made a mistake. It can NOT work.But thank you very much too. – Clxy May 18 '15 at 03:57
0

Finally, I found a way, not perfected but can work.

Since I can access the table at from segment. So, moving that function to from segment as a sub query will solved this problem.

SQL script likes below:

select t.*, f.* from my_table2 t join (select my_function(params)) f on true;

Still, all suggestions are welcome.

Clxy
  • 505
  • 1
  • 5
  • 13
0

The answer is function types.

By official docs

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

Clxy
  • 505
  • 1
  • 5
  • 13