Re: Converting PL/SQL to PL/PGSQL - Mailing list pgsql-hackers

Am Donnerstag, 10. Mai 2001 19:23 schrieb Roberto Mello:
> On Thu, May 10, 2001 at 03:33:27PM +0200, Klaus Reger wrote:
>     Have you looked at the "Porting From Oracle PL/SQL" chapter of the
> PostgreSQL Programmer's Guide? I am expanding that guide to include more
> things, like queries. The goas is for it to become a "Porting From
> Oracle" guide.
Yes I did, and it was very helpful for me. Thank you for this stuff. I made a 
list of the differences I found for me too. If you want it, I cam send it to 
you.


> > Writing PS/PGSQL tools seems to be a bit hard, because of the existing
> > tool-infrastructure on linux. Are there are tools I have overseen?
>     Heh? What do you mean by this? There are zillions of editors, both
> console and graphical, where you can do this.
Ah! That is right, I use emacs too.
>     I have found pgaccess to be vey useful in testing. In the OpenACS
> project (www.openacs.org) we port thousands of lines of Oracle code to
> PostgreSQL, mostly using vim or Emacs.
>     For testing, I use pgaccess because it lets me drop/recreate a
> function easily, plus it escapes quotes. One thing I don't like about it
> is that it's hard to keep things indented.
The problem for me seems, that the code is in the database. When you want to 
edit it, you do this in three steps:
1. Get source from the database
2. Edit the source
3. Put it back to the database

When there are no syntax-problems in the proc-declarations, or any wrong 
nested things step 3 is no problem. But often, when I ram my procedures I get 
runtime-errors (without konowing, where the problem exactly is). So here some 
type of compilation would be very useful.

First, I used pgacess too. because it is very helpful to develop 
pl/pgsql-procedures. But as the maintainer of my own Web-database-frontend I 
decided to write my own tool, which is very similar to pgaccess.

>
> > - A WWWdb-Application for editing and testing of SQL-Procedures over a
> >   WEB-frontend
>     Cool. Anywhere we can see this in action?
WWWdb of course. Point your browser to http://WWWdb.org. The procedure part 
is very sensible (because I don't want everybody to change my procedures :-), 
so it is not testable on my site. I may send you some screenshots, or you 
could install WWWdb at your computer and I send you the code separately, 
because it is not released as OpenSource yet.

> > - A perl-script, that does basic conversions between PL/SQL <-> XML <->
> >   PL/PGSQL (The Procedure-definition is converted completely, the
> > code-block a little bit)
>
>     Hmmm. *Very* interesting. Link? Source for this anywhere? We could
> probably use this at OpenACS.
I asked my boss, if he allows me to give out the sources, I will start a 
project at sourceforge. Stay tuned.

In this way it is called:
------------------------------------------------------------------------------------------
work@pc01:SqlProc$ ConvertPlsql.pl -h

   Call:       ConvertPlsql.pl [-DVw] [-o file] [file ...]
   Switches:       -D   Debugging-mode       -V   show version       -o file            <file> is the file where the
outputshould be directed to.            If <file> is a directory, one source-file will be generated            for
everyprocedure. When <file> is a normal file, all output            will be generated into this single file. Default is
STDOUT,           which can be passed explicitly as '-'       -s            Sort functions alphabetically at output
(Defaultis unsorted)       -S Source-language            This is the language of the existing script-file(s).
Valid values are (Default is PL_SQL):            - pl_sql       -T Target-language            This is the language of
thegenerated script-file(s).            Valid values are (Default is PL_PGSQL):            - xml            - pl_pgsql
    -w   Display warnings, that are found in conversion-process   Description:       ConvertPlsql.pl scans
PL/SQL-Procedure-definitionsand tries       to convert them to PL/PGSQL.
 

Here is an example of the conversion between Oracle, Postgres and XML:

------------------------------------------------------------------------------------------

<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE SOURCE SYSTEM "./SqlProc.dtd"><SOURCE><FUNCTION NAME       = "chk_ip" TYPE       = "FUNCTION" RESULTTYPE =
"NUMBER"> <PARAMETER   NAME  = "IPADRESSp"   INOUT = "IN"   TYPE  = "VARCHAR,"/>  <PARAMETER   NAME  = "N_uid"   INOUT
="IN"   TYPE  = "NUMBER,"/>  <VARIABLE   NAME = "N_tmp"   TYPE = "NUMBER"/>  <CODE>
 

  SELECT test.NEXTVAL INTO N_uid  /* FROM DUAL */ ;
  N_tmp := 'That''s my quoted text!';
  RETURN N_tmp;

EXCEPTION  WHEN others THEN      return -100;  </CODE></FUNCTION>
</SOURCE>

------------------------------------------------------------------------------------------

DROP FUNCTION chk_ip (VARCHAR, NUMBER,);
CREATE FUNCTION chk_ip (VARCHAR, NUMBER,)
RETURNS INTEGER AS '
DECLARE   IPADRESSp            ALIAS FOR $1;   N_uid                ALIAS FOR $2;   N_tmp                INTEGER;
BEGIN


  SELECT nextval(''test'') INTO N_uid  /* FROM DUAL */ ;
  N_tmp := ''That''''s my quoted text!'';
  RETURN N_tmp;

-- ORA -- EXCEPTION
-- ORA --    WHEN others THEN
-- ORA --        return -100;
END;
'  language 'plpgsql';

------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION chk_ip  (   IPADRESSp            IN VARCHAR2,   N_uid                IN NUMBER   )   RETURN
NUMBERIS   N_tmp                NUMBER;
 
BEGIN
  SELECT test.NEXTVAL INTO N_uid FROM dual;
  N_tmp := 'That''s my quoted text!';
  RETURN N_tmp;

EXCEPTION  WHEN others THEN      return -100;
END;
/

------------------------------------------------------------------------------------------

Regards, Klaus