Thread: 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, 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
forthe same.
 

Thanks,
Mamatha

On Tue, Sep 06, 2011 at 12:17:28PM +0000, Mamatha_Kagathi_Chan@DELL.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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.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
migrateddatabase. 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@DELL.com> wrote:

> Hi ,
>
> I have migrated Sql Server 2005 database to PostgreSQL 9.0 . I was also successfully able to connect classic ASP to
themigrated 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
sectionnamed "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@DELL.com> wrote:
>
>> Hi ,
>>
>> I have migrated Sql Server 2005 database to PostgreSQL 9.0 . I was also successfully able to connect classic ASP to
themigrated 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
functionin 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
parsingit eventually like it should for a function. Is it a bug in the driver that it cannot handle procedure from the
ODBCconnection?? 

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
fora 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@dell.com','12/10/2010','','7','Active','','Bangalore','IG','906','Muralikrishna','TG-,'TPDBA01','TPDBA01-
DBAPractice','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
givenname 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@ringerc.id.au]
Sent: Tuesday, October 25, 2011 8:30 AM
To: Alban Hertroys
Cc: Chan, Mamatha Kagathi; pgsql-general@postgresql.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@DELL.com> wrote:
>
>> Hi ,
>>
>> I have migrated Sql Server 2005 database to PostgreSQL 9.0 . I was also successfully able to connect classic ASP to
themigrated 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
PostgreSQLit 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
sendinga 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@DELL.com wrote:
> Hi Alban/Craig,
>
> Employeedetailinsert is procedure I have created in PostgreSQL. When I try to execute the procedure directly in
Pgadminas 
>
> 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
functionin 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@dell.com','12/10/2010','','7','Active','','Bangalore','IG','906','Muralikrishna','TG-,'TPDBA01','TPDBA01-
DBAPractice','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
givenname 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@dell.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@DELL.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@DELL.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@DELL.com wrote:
> [...]
> > I can also get the same result if I execute it in pgadmin (version downloaded from postgres community) which is on
adifferent 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@DELL.com wrote:
> > [...]
> > > I can also get the same result if I execute it in pgadmin (version downloaded from postgres community) which is
ona 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

Hi Craig,

The procedure definition is
CREATE OR REPLACE PROCEDURE employeedetailinsert(dellbadgeid  character varying DEFAULT NULL::character varying, empid
charactervarying 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::charactervarying, statuscd  character varying DEFAULT NULL::character varying, costcenter  character varying
DEFAULTNULL::character varying, "location"  character varying DEFAULT NULL::character varying, grade  character varying
DEFAULTNULL::character varying, hrmgrid  character varying DEFAULT NULL::character varying, hrorgmgr  character varying
DEFAULTNULL::character varying, hrdepartment  character varying DEFAULT NULL::character varying, financedepartment
charactervarying DEFAULT NULL::character varying, financedepartmentdescription  character varying DEFAULT
NULL::charactervarying, passport  character varying DEFAULT NULL::character varying, passvalidfrom  character varying
DEFAULTNULL::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-ASPpage 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
Idoubt 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@ringerc.id.au]
Sent: Wednesday, December 07, 2011 1:48 PM
To: Chan, Mamatha Kagathi
Cc: haramrae@gmail.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] [pgadmin-support] Help for Migration

On 12/07/2011 03:23 PM, Mamatha_Kagathi_Chan@DELL.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
psqlcommand 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
functionin 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
suchthing 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@dell.com','12/10/2010','','7','Active','','Bangalore','IG','906','Muralikrishna','TG-,'TPDBA01','TPDBA01-
DBAPractice','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
givenname 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
functionsin 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'vegot the wrong data types; 

- You might need to specify explicit data types for your parameters in the ODBC call, eg 'Active'::text . Please show
yourfunction definition (at least the line with "CREATE OR REPLACE FUNCTION ( parameters ) RETURNS ..." on it) so we
cansee 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
beingsearched; 

- .... ?


Please show your function definition.

--
Craig Ringer

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
Iam able to create procedure and execute them in pgadmin and psql. This piece of code was migrated from Oracle database
(MS-SQLto Oracle then Oracle to Postgres). Nevertheless I am able to create independently a procedure also. Only In the
ASPenvironmnet 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@dell.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@gmail.com]
Sent: Wednesday, December 07, 2011 2:56 PM
To: Chan, Mamatha Kagathi
Cc: ringerc@ringerc.id.au; pgsql-general@postgresql.org
Subject: Re: [GENERAL] [pgadmin-support] Help for Migration

On 7 December 2011 10:08,  <Mamatha_Kagathi_Chan@dell.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
wrapperaround 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.

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
differentclient 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@ringerc.id.au]
Sent: Wednesday, December 07, 2011 3:37 PM
To: Chan, Mamatha Kagathi
Cc: haramrae@gmail.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] [pgadmin-support] Help for Migration

On 12/07/2011 05:44 PM, Mamatha_Kagathi_Chan@DELL.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