Re: [pgadmin-support] Help for Migration

Hi,

I am trying to migrate a very small MS SQL Server Database (with 200 records max, 20 tables) to PostgreSQL. I tried browsing through internet to find technical steps but I found some blogs with vague discussion for same. Kindly let me know where to look for the information or even better if I can get some document from the community for the same.

Thanks,
Mamatha

Hi,

On Mon, 2011-09-05 at 06:47 +0000, Mamatha_Kagathi_Chan(at)DELL(dot)com wrote:
> Hi,
>
> I am trying to migrate a very small MS SQL Server Database (with 200 records max, 20 tables) to PostgreSQL. I tried browsing through internet to find technical steps but I found some blogs with vague discussion for same. Kindly let me know where to look for the information or even better if I can get some document from the community for the same.
>

You should better ask on pgsql-general. This is a pgAdmin mailing list,
not a PostgreSQL one.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Hi,

I am trying to migrate a very small MS SQL Server Database (with 200 records max, 20 tables, 10 stored procedures) to PostgreSQL. I tried browsing through internet to find technical steps but I found some blogs with vague discussion for same. Kindly let me know where to look for the information or even better if I can get some document from the community for the same.

Thanks,
Mamatha

Hi,

I am trying to migrate a very small MS SQL Server Database (with 200 records max, 20 tables, 10 stored procedures) to PostgreSQL. I tried browsing through internet to find technical steps but I found some blogs with vague discussion for same. Kindly let me know where to look for the information or even better if I can get some document from the community for the same.

Thanks,
Mamatha

On Tue, Sep 06, 2011 at 12:17:28PM +0000, Mamatha_Kagathi_Chan(at)DELL(dot)com wrote:
> Hi,
>
> I am trying to migrate a very small MS SQL Server Database (with 200
> records max, 20 tables, 10 stored procedures) to PostgreSQL. I tried
> browsing through internet to find technical steps but I found some
> blogs with vague discussion for same. Kindly let me know where to
> look for the information or even better if I can get some document
> from the community for the same.

The table structures shouldn't be a problem to do fairly mechanically,
and at worst you can simply hand-type in the data. The stored
procedures will be a problem at two levels:

1. You'll have to translate them into a language PostgreSQL can use
from (I'm guessing here, but it's usually a good guess in these
situations) T-SQL.

2. PostgreSQL functions, which are similar in many ways to stored
procedures, have a fundamental difference: they can't control
transactions. Any stored procedures that have a COMMIT or ROLLBACK in
them will have to be re-architected in a fundamental way.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Hi ,

I have migrated Sql Server 2005 database to PostgreSQL 9.0 . I was also successfully able to connect classic ASP to the migrated database. But when the page reaches a stage where it is accessing a procedure I get the following error.

PostgreSQL Native Provider error '80040e14'

ERROR: syntax error at or near "' 789500'" LINE 1: EXEC Visadetailinsert ' 789500','No','Select visa','','','Se... ^

/postgresql/EmployeeDetails.asp, line 645

Where as the object is in valid state when seen from Pgadmin.

Kindly advice what I should check next to resolve this issue.

Thanks,
Mamatha

On 24 Oct 2011, at 14:24, <Mamatha_Kagathi_Chan(at)DELL(dot)com> wrote:

> Hi ,
>
> I have migrated Sql Server 2005 database to PostgreSQL 9.0 . I was also successfully able to connect classic ASP to the migrated database. But when the page reaches a stage where it is accessing a procedure I get the following error.
>
> PostgreSQL Native Provider error '80040e14'
>
> ERROR: syntax error at or near "' 789500'" LINE 1: EXEC Visadetailinsert ' 789500','No','Select visa','','','Se... ^
>
> /postgresql/EmployeeDetails.asp, line 645

Is that a line in a plpgsql procedure? It looks like you're trying to call a function, but your syntax is entirely wrong.

You probably want to read up on: http://www.linuxtopia.org/online_books/database_guides/Practical_PostgreSQL_database/PostgreSQL_x20238_004.htm

Curiously enough the rather fine manual isn't very verbose on this subject. In the 9.2-devel docs there is even a section named "Calling functions", but it seems to focus on writing functions instead.

Alban Hertroys

--
The scale of a problem often equals the size of an ego.

On 25/10/11 03:36, Alban Hertroys wrote:
> On 24 Oct 2011, at 14:24, <Mamatha_Kagathi_Chan(at)DELL(dot)com> wrote:
>
>> Hi ,
>>
>> I have migrated Sql Server 2005 database to PostgreSQL 9.0 . I was also successfully able to connect classic ASP to the migrated database. But when the page reaches a stage where it is accessing a procedure I get the following error.
>>
>> PostgreSQL Native Provider error '80040e14'
>>
>> ERROR: syntax error at or near "' 789500'" LINE 1: EXEC Visadetailinsert ' 789500','No','Select visa','','','Se... ^

What database access driver/method are you using?

PostgreSQL doesn't support 'EXEC' ... you probably want to

SELECT myfunctionname(argument1, argument2);

instead. If you're using ODBC, you should be using the ODBC
procedure-call escape sequence to make it database-agnostic, eg:

{? = CALL procname(?,?)}

See: http://msdn.microsoft.com/en-us/library/ms403294.aspx

The ODBC driver for the database you're using should translate that into
suitable call syntax for your database, eg in PostgreSQL it should produce

SELECT procname(?,?);

and capture the result.

I was initially suspicious that the issue was the whitespace in the
quoted number, but that's not it. You're simply sending a completely
bogus query string, and that's the first point the parser happens to
choke on.

--
Craig Ringer

Hi Alban/Craig,

Employeedetailinsert is procedure I have created in PostgreSQL. When I try to execute the procedure directly in Pgadmin as

EXEC Employeedetailinsert (parameters same as below) It works fine with desired result.

But When I call the procedure in Classic ASP I get the below error. I do not understand why the driver assumes it as function in the 1st place. I am using Postgres Native driver .
In the call when I am using CALL as suggested below , I am still getting the same error as the driver seem to be parsing it eventually like it should for a function. Is it a bug in the driver that it cannot handle procedure from the ODBC connection??

Pls let me know.

The entire error as I am getting is as follows. Pls note that database is not recognizing the object as it is looking for a function with that name. But here it is a procedure I am executing.
-error start
Executing Procedure =EXEC employeedetailinsert( ' 123','55','Mamatha','Chandrashekar','06','05','9886269427','mamatha_ka(at)dell(dot)com','12/10/2010','','7','Active','','Bangalore','IG','906','Muralikrishna','TG-,'TPDBA01','TPDBA01- DBA Practice','No','','')
PostgreSQL Native Provider error '80040e14'

ERROR: function employeedetailinsert(unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown) does not exist LINE 1: SELECT * FROM employeedetailinsert(' 123','55','Mamat... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

/postgresql/EmployeeDetails.asp, line 656
-error end

Thanks,
Mamatha

-----Original Message-----
From: Craig Ringer [mailto:ringerc(at)ringerc(dot)id(dot)au]
Sent: Tuesday, October 25, 2011 8:30 AM
To: Alban Hertroys
Cc: Chan, Mamatha Kagathi; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] [pgadmin-support] Help for Migration

On 25/10/11 03:36, Alban Hertroys wrote:
> On 24 Oct 2011, at 14:24, <Mamatha_Kagathi_Chan(at)DELL(dot)com> wrote:
>
>> Hi ,
>>
>> I have migrated Sql Server 2005 database to PostgreSQL 9.0 . I was also successfully able to connect classic ASP to the migrated database. But when the page reaches a stage where it is accessing a procedure I get the following error.
>>
>> PostgreSQL Native Provider error '80040e14'
>>
>> ERROR: syntax error at or near "' 789500'" LINE 1: EXEC
>> Visadetailinsert ' 789500','No','Select visa','','','Se... ^

What database access driver/method are you using?

PostgreSQL doesn't support 'EXEC' ... you probably want to

SELECT myfunctionname(argument1, argument2);

instead. If you're using ODBC, you should be using the ODBC procedure-call escape sequence to make it database-agnostic, eg:

{? = CALL procname(?,?)}

See: http://msdn.microsoft.com/en-us/library/ms403294.aspx

The ODBC driver for the database you're using should translate that into suitable call syntax for your database, eg in PostgreSQL it should produce

SELECT procname(?,?);

and capture the result.

I was initially suspicious that the issue was the whitespace in the quoted number, but that's not it. You're simply sending a completely bogus query string, and that's the first point the parser happens to choke on.

--
Craig Ringer

On 12/07/2011 03:23 PM, Mamatha_Kagathi_Chan(at)DELL(dot)com wrote:
> Hi Alban/Craig,
>
> Employeedetailinsert is procedure I have created in PostgreSQL. When I try to execute the procedure directly in Pgadmin as
>
> EXEC Employeedetailinsert (parameters same as below) It works fine with desired result.
PgAdmin uses libpq directly. It must be translating the `EXEC' into
something PostgreSQL can understand, because the psql command line tool
(which also uses libpq) doesn't know what EXEC means.

regress=> EXEC dummyfunction();
ERROR: syntax error at or near "EXEC"
LINE 1: EXEC dummyfunction();

> But When I call the procedure in Classic ASP I get the below error. I do not understand why the driver assumes it as function in the 1st place. I am using Postgres Native driver .
Since you're talking about ODBC, I presume you're *actually* using
PsqlODBC as your database driver. AFAIK there's no such thing as
"postgres native driver".

> In the call when I am using CALL as suggested below
OK, and since you're using ODBC the CALL gets translated to a
server-side proc invocation, that should be no problem.

You're not actually showing your ODBC code or ODBC query string, which
would be helpful.

> Executing Procedure =EXEC employeedetailinsert( ' 123','55','Mamatha','Chandrashekar','06','05','9886269427','mamatha_ka(at)dell(dot)com','12/10/2010','','7','Active','','Bangalore','IG','906','Muralikrishna','TG-,'TPDBA01','TPDBA01- DBA Practice','No','','')
> PostgreSQL Native Provider error '80040e14'
>
> ERROR: function employeedetailinsert(unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown) does not exist LINE 1: SELECT * FROM employeedetailinsert(' 123','55','Mamat... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Possibilities:

- You might have defined your function with a double-quoted name, eg
"Employeedetailinsert". This makes it case sensitive. If that's the
case, you'll see it with a capital letter when you run the "\df" command
in psql or browse functions in PgAdmin;

- You've mucked up your argument list and it isn't really the same
length as what you used in PgAdmin after all or you've got the wrong
data types;

- You might need to specify explicit data types for your parameters in
the ODBC call, eg 'Active'::text . Please show your function definition
(at least the line with "CREATE OR REPLACE FUNCTION ( parameters )
RETURNS ..." on it) so we can see what you're actually trying to call;

- You've set a search_path that means that your ODBC call can't find the
function because it's in a schema that isn't being searched;

- .... ?

Please show your function definition.

--
Craig Ringer

Hi Craig,

The procedure definition is
CREATE OR REPLACE PROCEDURE employeedetailinsert(dellbadgeid character varying DEFAULT NULL::character varying, empid character varying DEFAULT NULL::character varying, firstname character varying DEFAULT NULL::character varying, lastname character varying DEFAULT NULL::character varying, dob_day character varying DEFAULT NULL::character varying, dob_month character varying DEFAULT NULL::character varying, contactno character varying DEFAULT NULL::character varying, email character varying DEFAULT NULL::character varying, hiredate character varying DEFAULT NULL::character varying, rehiredate character varying DEFAULT NULL::character varying, totalexp character varying DEFAULT NULL::character varying, statuscd character varying DEFAULT NULL::character varying, costcenter character varying DEFAULT NULL::character varying, "location" character varying DEFAULT NULL::character varying, grade character varying DEFAULT NULL::character varying, hrmgrid character varying DEFAULT NULL::character varying, hrorgmgr character varying DEFAULT NULL::character varying, hrdepartment character varying DEFAULT NULL::character varying, financedepartment character varying DEFAULT NULL::character varying, financedepartmentdescription character varying DEFAULT NULL::character varying, passport character varying DEFAULT NULL::character varying, passvalidfrom character varying DEFAULT NULL::character varying, passvalidupto character varying DEFAULT NULL::character varying) AS...

-- So I am not calling a function but a procedure.

The ODBC string is:
conn.Open "Provider=PGNP.1;Password=PGDBTest5;Persist Security Info=True;User ID=enterprisedb;Initial Catalog=edb;Data Source=<ip_address>;PORT=5444;SSL=allow;"

here <IP address> is replaced for discretion.

I have all reasons to believe that this connection string works as I am able to connect to other pages where the database-ASP page interaction is directly using select, insert and update statements rather than calling procedures.

Finally I have copy-pasted the "exec proc" form the error in Pgadmin and executed the same to get desirable results. So I doubt if the parameters have anything to do with this.

Hope I have provided all the information. Please let me know if I am still lost somewhere.

Thanks,
Mamatha

-----Original Message-----
From: Craig Ringer [mailto:ringerc(at)ringerc(dot)id(dot)au]
Sent: Wednesday, December 07, 2011 1:48 PM
To: Chan, Mamatha Kagathi
Cc: haramrae(at)gmail(dot)com; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] [pgadmin-support] Help for Migration

On 12/07/2011 03:23 PM, Mamatha_Kagathi_Chan(at)DELL(dot)com wrote:
> Hi Alban/Craig,
>
> Employeedetailinsert is procedure I have created in PostgreSQL. When I
> try to execute the procedure directly in Pgadmin as
>
> EXEC Employeedetailinsert (parameters same as below) It works fine with desired result.
PgAdmin uses libpq directly. It must be translating the `EXEC' into something PostgreSQL can understand, because the psql command line tool (which also uses libpq) doesn't know what EXEC means.

regress=> EXEC dummyfunction();
ERROR: syntax error at or near "EXEC"
LINE 1: EXEC dummyfunction();

> But When I call the procedure in Classic ASP I get the below error. I do not understand why the driver assumes it as function in the 1st place. I am using Postgres Native driver .
Since you're talking about ODBC, I presume you're *actually* using PsqlODBC as your database driver. AFAIK there's no such thing as "postgres native driver".

> In the call when I am using CALL as suggested below
OK, and since you're using ODBC the CALL gets translated to a server-side proc invocation, that should be no problem.

You're not actually showing your ODBC code or ODBC query string, which would be helpful.

> Executing Procedure =EXEC employeedetailinsert( '
> 123','55','Mamatha','Chandrashekar','06','05','9886269427','mamatha_ka(at)dell(dot)com','12/10/2010','','7','Active','','Bangalore','IG','906','Muralikrishna','TG-,'TPDBA01','TPDBA01- DBA Practice','No','','') PostgreSQL Native Provider error '80040e14'
>
> ERROR: function employeedetailinsert(unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown) does not exist LINE 1: SELECT * FROM employeedetailinsert(' 123','55','Mamat... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Possibilities:

- You might have defined your function with a double-quoted name, eg "Employeedetailinsert". This makes it case sensitive. If that's the case, you'll see it with a capital letter when you run the "\df" command in psql or browse functions in PgAdmin;

- You've mucked up your argument list and it isn't really the same length as what you used in PgAdmin after all or you've got the wrong data types;

- You might need to specify explicit data types for your parameters in the ODBC call, eg 'Active'::text . Please show your function definition (at least the line with "CREATE OR REPLACE FUNCTION ( parameters ) RETURNS ..." on it) so we can see what you're actually trying to call;

- You've set a search_path that means that your ODBC call can't find the function because it's in a schema that isn't being searched;

- .... ?

Please show your function definition.

--
Craig Ringer

On 7 December 2011 10:08, <Mamatha_Kagathi_Chan(at)dell(dot)com> wrote:
> The procedure definition is
> CREATE OR REPLACE PROCEDURE

> -- So I am not calling a function but a procedure.

I don't think CREATE PROCEDURE is actually a valid command in
Postgres. The 9.0 documentation seems to confirm that
(http://www.postgresql.org/docs/9.0/static/sql-commands.html).

In Postgres, "procedures" are void-returning functions - there's no
difference. It's just a naming convention.

Perhaps you're using some 3rd party code for MS-SQL compatibility? I
imagine CREATE PROCEDURE would then be a simple wrapper around CREATE
FUNCTION ... RETURNING void.

Heh, didn't know you could define DEFAULT argument values like that,
but it seems you can!
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

I am using postgres 9.0.4 . I can actually use EXEC in psql which seem to be throwing some error in Craig's case. Also I am able to create procedure and execute them in pgadmin and psql. This piece of code was migrated from Oracle database (MS-SQL to Oracle then Oracle to Postgres). Nevertheless I am able to create independently a procedure also. Only In the ASP environmnet I am facing issue.

Below is the piece of code which I executed in Psql.

edb=# EXEC employeedetailinsert( '529','55','Mamatha','Chandrashekar','0
9','03','9677756894','mamatha_ka(at)dell(dot)com','12/10/2010','','7','Active','','Bang
alore','IG','206','Muralikrishna','TG-DBA','TPDB','TPDB','No','','');

EDB-SPL Procedure successfully completed

-----Original Message-----
From: Alban Hertroys [mailto:haramrae(at)gmail(dot)com]
Sent: Wednesday, December 07, 2011 2:56 PM
To: Chan, Mamatha Kagathi
Cc: ringerc(at)ringerc(dot)id(dot)au; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] [pgadmin-support] Help for Migration

On 7 December 2011 10:08, <Mamatha_Kagathi_Chan(at)dell(dot)com> wrote:
> The procedure definition is
> CREATE OR REPLACE PROCEDURE

> -- So I am not calling a function but a procedure.

I don't think CREATE PROCEDURE is actually a valid command in Postgres. The 9.0 documentation seems to confirm that (http://www.postgresql.org/docs/9.0/static/sql-commands.html).

In Postgres, "procedures" are void-returning functions - there's no difference. It's just a naming convention.

Perhaps you're using some 3rd party code for MS-SQL compatibility? I imagine CREATE PROCEDURE would then be a simple wrapper around CREATE FUNCTION ... RETURNING void.

Heh, didn't know you could define DEFAULT argument values like that, but it seems you can!
--
If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.

On 12/07/2011 05:44 PM, Mamatha_Kagathi_Chan(at)DELL(dot)com wrote:
> I am using postgres 9.0.4

I don't think you are, actually, I think you're using EnterpriseDB
Postgres Plus Advanced Server.

Please past the output of the "SELECT version();" command.

Here's what happens if you try "CREATE PROCEDURE" on PostgreSQL:

$ psql regress
psql (9.1.1)
Type "help" for help.

regress=> select version();
version
-------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.1 on x86_64-redhat-linux-gnu, compiled by gcc (GCC)
4.6.1 20110824 (Red Hat 4.6.1-8), 64-bit
(1 row)

regress=> CREATE OR REPLACE PROCEDURE test () RETURNS void AS $$
regress$> BEGIN
regress$> RETURN;
regress$> END;
regress$> $$ LANGUAGE 'plpgsql';
ERROR: syntax error at or near "PROCEDURE"
LINE 1: CREATE OR REPLACE PROCEDURE test () RETURNS void AS $$
^

> I can actually use EXEC in psql which seem to be throwing some error in Craig's case.
That's because it seems you're not actually using PostgreSQL.

You need to contact EnterpriseDB technical support for assistance if you
are using EnterpriseDB. If it's another product, please specify.

--
Craig Ringer

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?
And Postgres9.0 from community has a limitation for procedures?

I actually changed the stored procedure to Function and now I can get the desired result from psql, pgadmin and ASP consistently.

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.

Please let me know your thoughts so that I can understand the difference between the versions.

Thanks,
Mamatha

-----Original Message-----
From: Craig Ringer [mailto:ringerc(at)ringerc(dot)id(dot)au]
Sent: Wednesday, December 07, 2011 3:37 PM
To: Chan, Mamatha Kagathi
Cc: haramrae(at)gmail(dot)com; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] [pgadmin-support] Help for Migration

On 12/07/2011 05:44 PM, Mamatha_Kagathi_Chan(at)DELL(dot)com wrote:
> I am using postgres 9.0.4

I don't think you are, actually, I think you're using EnterpriseDB Postgres Plus Advanced Server.

Please past the output of the "SELECT version();" command.

Here's what happens if you try "CREATE PROCEDURE" on PostgreSQL:

$ psql regress
psql (9.1.1)
Type "help" for help.

regress=> select version();
version
-------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.1 on x86_64-redhat-linux-gnu, compiled by gcc (GCC)
4.6.1 20110824 (Red Hat 4.6.1-8), 64-bit
(1 row)

regress=> CREATE OR REPLACE PROCEDURE test () RETURNS void AS $$ regress$> BEGIN
regress$> RETURN;
regress$> END;
regress$> $$ LANGUAGE 'plpgsql';
ERROR: syntax error at or near "PROCEDURE"
LINE 1: CREATE OR REPLACE PROCEDURE test () RETURNS void AS $$
^

> I can actually use EXEC in psql which seem to be throwing some error in Craig's case.
That's because it seems you're not actually using PostgreSQL.

You need to contact EnterpriseDB technical support for assistance if you are using EnterpriseDB. If it's another product, please specify.

--
Craig Ringer

On 12/07/2011 06:57 PM, Mamatha_Kagathi_Chan(at)DELL(dot)com 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

On Wed, 2011-12-07 at 21:23 +0800, Craig Ringer wrote:
> On 12/07/2011 06:57 PM, Mamatha_Kagathi_Chan(at)DELL(dot)com wrote:
> [...]
> > 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 ?
>

pgAdmin doesn't translate queries executed by the user. If EXEC works on
EDB AS, then pgadmin will fire it with success. If it doesn't, pgadmin
will fire it, and the result will be a failure.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

On Wed, 2011-12-07 at 14:30 +0100, Guillaume Lelarge wrote:
> On Wed, 2011-12-07 at 21:23 +0800, Craig Ringer wrote:
> > On 12/07/2011 06:57 PM, Mamatha_Kagathi_Chan(at)DELL(dot)com wrote:
> > [...]
> > > 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 ?
> >
>
> pgAdmin doesn't translate queries executed by the user. If EXEC works on
> EDB AS, then pgadmin will fire it with success. If it doesn't, pgadmin
> will fire it, and the result will be a failure.
>

BTW, pgAdmin allows to open the query tool with an EXEC script when the
user has selected a procedure (EDB AS object). So I guess EDB AS
supports the EXEC statement :)

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

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(at)ringerc(dot)id(dot)au]
Sent: Wednesday, December 07, 2011 6:53 PM
To: Chan, Mamatha Kagathi
Cc: haramrae(at)gmail(dot)com; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] [pgadmin-support] Help for Migration

On 12/07/2011 06:57 PM, Mamatha_Kagathi_Chan(at)DELL(dot)com<mailto:Mamatha_Kagathi_Chan(at)DELL(dot)com> 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