Thread: Porting from PL/SQL to PLPGSQL
Hi All, We are in the process of migrating of our application from Oracle to PostGreSQL. we are facing a few problems with PL/SQL Code.. 1) In Oracle sqlplus we can run sql script files as @script_name;How do we do the same in PostGres.Also is there any replacementfor "&&" in PostGres ? Eg: CREATE USER CATALOG IDENTIFIED BY &ORA_PASSWORD DEFAULT TABLESPACE &DFLT_TABLESPACE TEMPORARY TABLESPACE&TEMP_TABLESPACE 2) In PostGres a function can not take more 16 arguments.We have some procedures which are taking more than 16 arguements.So how can we convert them into PostGres. Can anyone give some valuable suggestions.. Regards Joe.
On Wed, Aug 20, 2003 at 03:15:23PM +0530, Jomon Skariah wrote: > > we are facing a few problems with PL/SQL Code.. > > > 1) In Oracle sqlplus we can run sql script files as @script_name; > How do we do the same in PostGres. psql takes \i. See psql's \? See the "Porting From Oracle PL/SQL" document I wrote that is part of the PL/pgSQL documentation. > Also is there any replacement for "&&" in PostGres ? > > > Eg: > CREATE USER CATALOG > IDENTIFIED BY &ORA_PASSWORD > DEFAULT TABLESPACE &DFLT_TABLESPACE > TEMPORARY TABLESPACE &TEMP_TABLESPACE What does that do? > > 2) In PostGres a function can not take more 16 arguments.We have some > procedures which are taking more than > 16 arguements.So how can we convert them into PostGres. You can recompile to change the number of parameters. PG 7.3 can have up to 32 arguments by default, IIRC. -Roberto -- +----| Roberto Mello - http://www.brasileiro.net/ |------+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + It's now the GNU Emacs of all terminal emulators.-- Linus Torvalds, regarding the fact that Linux started off as a terminalemulator
Hi.. In postgres, you have two options.. From PSQL prompt, you can use " \i filename " to process the file.. OR from PSQL prompt, you can directly instruct to execute that file like.. psql ... --filename <<your_file_name>> Basically, when "&&" is specified in PL/SQL scripts, it prompts the user to enter its value. In Postgres, we do not have such option (so far i know). So, you can replace those with your desired values directly. Postgres supports Array type too. Although i haven't used it anyday.. you can try it out. HTH Denis ----- Original Message ----- From: "Jomon Skariah" <jomons@aztec.soft.net> To: <pgsql-sql@postgresql.org> Sent: Wednesday, August 20, 2003 3:15 PM Subject: [SQL] Porting from PL/SQL to PLPGSQL > > Hi All, > > We are in the process of migrating of our application from Oracle to > PostGreSQL. > > > we are facing a few problems with PL/SQL Code.. > > > 1) In Oracle sqlplus we can run sql script files as @script_name; > How do we do the same in PostGres. > Also is there any replacement for "&&" in PostGres ? > > > Eg: > CREATE USER CATALOG > IDENTIFIED BY &ORA_PASSWORD > DEFAULT TABLESPACE &DFLT_TABLESPACE > TEMPORARY TABLESPACE &TEMP_TABLESPACE > > > 2) In PostGres a function can not take more 16 arguments.We have some > procedures which are taking more than > 16 arguements.So how can we convert them into PostGres. > > > > Can anyone give some valuable suggestions.. > > > Regards > > Joe. > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend