Re: [pgadmin-support] Help for Migration

[<Mamatha_Kagathi_Chan@xxxxxxxx>]

Thanks everybody for helping me understand how postgres works with stored programs. I will remember in future to put certain information clearly as mentioned in the website suggested by Craig. Thanks for that as well.

 

From: Craig Ringer [mailto:ringerc@xxxxxxxxxxxxx]
Sent: Wednesday, December 07, 2011 6:53 PM
To: Chan, Mamatha Kagathi
Cc: haramrae@xxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx
Subject: Re: [pgadmin-support] Help for Migration

 

On 12/07/2011 06:57 PM, Mamatha_Kagathi_Chan@xxxxxxxx wrote:

HI Craig,

Yes I am using EnterpriseDB Postgres Plus Advanced Server.

But does that mean Postgres 9.0 version from Postgres community and Postgres9.0 version from EnterpriseDB works differently?

Yes! They're different things. EnterpriseDB adds an Oracle compatibility layer, stored procedures, and all sorts of other little extras. If they were the same, why would people pay for EnterpriseDB Advanced Server? They might pay for support, but not an up-front license fee for a product where they could download it for free...

You still haven't posted "select version()". That is one of the first items in this page:

  http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

... which I strongly suggest that you read, because following it would've saved all of us a lot of hassle and confusion.

And Postgres9.0 from community has a limitation for procedures?

Yes!

PostgreSQL (as of version 9.1 at least) has NO support for stored procedures. It supports user-defined stored functions in a variety of languages, but no stand-alone procedures. It emulates stored procedures by invoking a stored function stand-alone as, eg:

SELECT somefuncname();

but those functions can't do things like BEGIN/COMMIT, etc.

I can also get the same result if I execute it in pgadmin (version downloaded from postgres community) which is on a different client machine but connected to the server on enterpriseDB version

As EXEC proc.

If you're connected to EnterpriseDB, I'd expect that.

If you're connected to PostgreSQL, maybe PgAdmin is translating EXEC into a SELECT ?

--
Craig Ringer