0

We have a schema in Greenplum with Postgres version 8.2, where we are not allowed to create a function. We have some working functions in another server/schema. We like to run those functions in a new schema as anonymous code block. We tried the following in pgadmin (Gui interface) and command line psql. We're getting an error.

Here is the tried code.

do $$
  declare
    at1 text;
    ftxt varchar(50) := ‘How are you?’ ;
  begin
    RAISE NOTICE ‘Hello World ‘ || ftxt ;
  end;
$$;

It is not accepting "do". That's where the error points. I need your guidance to make this work.

Is there a mechanism like DB link of Oracle to work with across greenplum instances/schema objects?

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
user2647763 - RIMD
  • 74
  • 1
  • 11

1 Answers1

2

Greenplum isn't PostgreSQL, it's a separate product based on PostgreSQL 8.2.

DO was added in PostgreSQL 9.0. So this won't work on Greenplum. You must create then execute a function. If you don't have the rights to do that, you're just out of luck, you cannot use PL/PgSQL.

PostgreSQL supports DBLink (see dblink in the docs) for cross-database connections. I have no idea whether it's supported by Greenplum.

In future questions ensure that:

  • You tag them greenplum as well as postgresql
  • If you get an error, you give the exact text of the error message.

Thanks for including the code and mentioning that you were using PostgreSQL and Greenplum, though.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Thanks for your reply/suggestion. The DBLINK is pointing to PostgreSQL 9.0. **Is DBLINK available in PostgreSQL 8.2?** – user2647763 - RIMD Nov 09 '13 at 19:07
  • @user2647763 Actually, that link points to the /current/ version documentation. See the version links along the top? Click on the one for 8.2. If 8.2 doesn't appear in the list (it doesn't for dblink) then that documentation page didn't exist in 8.2. So dblink wasn't a built-in contrib in 8.2; it might've been available as an external 3rd party module, but that probably won't do you any good with Greenplum. – Craig Ringer Dec 08 '13 at 00:40