pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPorting from PL/SQL to PLPGSQL
8+ messages / 5 participants
[nested] [flat]
* Porting from PL/SQL to PLPGSQL
@ 2003-08-13 13:44 Jomon Skariah <jomons@aztec.soft.net>
0 siblings, 2 replies; 8+ messages in thread
From: Jomon Skariah @ 2003-08-13 13:44 UTC (permalink / raw)
To: pgsql-sql
Hi,
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 Exceptions ORACLE have something called WHEN OTHERS THEN cluase.I
can't find a replacement for that in
PostGreSQL.
2 Oracle have a function USERENV to get the user session
information.Is there any replacement available in PostGres. ?
3 Is there any replace available for INSTEAD OF INSERT/DELETE/UPDATE
for triggers in PostGreSQL.
Regards
Jomon Skariah.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Porting from PL/SQL to PLPGSQL
@ 2003-08-13 19:35 Josh Berkus <josh@agliodbs.com>
parent: Jomon Skariah <jomons@aztec.soft.net>
1 sibling, 0 replies; 8+ messages in thread
From: Josh Berkus @ 2003-08-13 19:35 UTC (permalink / raw)
To: Jomon Skariah <jomons@aztec.soft.net>; pgsql-sql
Jomon,
> 1) In Exceptions ORACLE have something called WHEN OTHERS THEN cluase.I
> can't find a replacement for that in
> PostGreSQL.
PL/pgSQL currently does not handle exceptions at all. This is on the TODO
list.
> 2 Oracle have a function USERENV to get the user session
> information.Is there any replacement available in PostGres. ?
There are several variables and tools which provide current user and session
information. See the "PostgreSQL Administration" section of the
documentation.
> 3 Is there any replace available for INSTEAD OF INSERT/DELETE/UPDATE
> for triggers in PostGreSQL.
In Postgres, this is generally done through the RULES system instead of
triggers. Please lookup CREATE RULE in the online docs.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Porting from PL/SQL to PLPGSQL
@ 2003-08-13 19:50 Rod Taylor <rbt@rbt.ca>
parent: Jomon Skariah <jomons@aztec.soft.net>
1 sibling, 0 replies; 8+ messages in thread
From: Rod Taylor @ 2003-08-13 19:50 UTC (permalink / raw)
To: Jomon Skariah <jomons@aztec.soft.net>; +Cc: pgsql-sql
> we are facing a few problems with PL/SQL Code..
>
> 1) In Exceptions ORACLE have something called WHEN OTHERS THEN cluase.I
> can't find a replacement for that in
> PostGreSQL.
What does it do?
> 2 Oracle have a function USERENV to get the user session
> information.Is there any replacement available in PostGres. ?
CURRENT_USER (I believe this is per spec)
> 3 Is there any replace available for INSTEAD OF INSERT/DELETE/UPDATE
> for triggers in PostGreSQL.
You might have some luck with Rules (CREATE RULE) if you are writing
data to a different table. Otherwise you're stuck with somewhat more
complicated triggers.
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Porting from PL/SQL to PLPGSQL
@ 2003-08-14 08:19 Jomon Skariah <jomons@aztec.soft.net>
0 siblings, 0 replies; 8+ messages in thread
From: Jomon Skariah @ 2003-08-14 08:19 UTC (permalink / raw)
To: josh@agliodbs.com; pgsql-sql
Hi Josh,
Thanks for your reply.
In our PL/SQL code we have used userenv('LANG') in some of the INSERT
statements.
Do we have a replacement for this..
Regards
Jomon
-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Thursday, August 14, 2003 1:05 AM
To: Jomon Skariah; pgsql-sql@postgresql.org
Subject: Re: [SQL] Porting from PL/SQL to PLPGSQL
Jomon,
> 1) In Exceptions ORACLE have something called WHEN OTHERS THEN cluase.I
> can't find a replacement for that in
> PostGreSQL.
PL/pgSQL currently does not handle exceptions at all. This is on the TODO
list.
> 2 Oracle have a function USERENV to get the user session
> information.Is there any replacement available in PostGres. ?
There are several variables and tools which provide current user and session
information. See the "PostgreSQL Administration" section of the
documentation.
> 3 Is there any replace available for INSTEAD OF INSERT/DELETE/UPDATE
> for triggers in PostGreSQL.
In Postgres, this is generally done through the RULES system instead of
triggers. Please lookup CREATE RULE in the online docs.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Porting from PL/SQL to PLPGSQL
@ 2003-08-14 13:46 Jomon Skariah <jomons@aztec.soft.net>
0 siblings, 0 replies; 8+ messages in thread
From: Jomon Skariah @ 2003-08-14 13:46 UTC (permalink / raw)
To: pgsql-sql
> Hi,
>
> 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) Can we perform DML statements on VIEWS in PostGreSQL as we do in
> ORACLE?
> Do I need to create some RULES for that ??
>
> 2 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
>
>
> Regards
>
> Jomon Skariah.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Porting from PL/SQL to PLPGSQL
@ 2003-08-20 09:45 Jomon Skariah <jomons@aztec.soft.net>
0 siblings, 2 replies; 8+ messages in thread
From: Jomon Skariah @ 2003-08-20 09:45 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Porting from PL/SQL to PLPGSQL
@ 2003-08-20 21:12 Roberto Mello <rmello@cc.usu.edu>
parent: Jomon Skariah <jomons@aztec.soft.net>
1 sibling, 0 replies; 8+ messages in thread
From: Roberto Mello @ 2003-08-20 21:12 UTC (permalink / raw)
To: Jomon Skariah <jomons@aztec.soft.net>; +Cc: pgsql-sql
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 terminal emulator
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Porting from PL/SQL to PLPGSQL
@ 2003-08-22 06:27 denis@coralindia.com
parent: Jomon Skariah <jomons@aztec.soft.net>
1 sibling, 0 replies; 8+ messages in thread
From: denis@coralindia.com @ 2003-08-22 06:27 UTC (permalink / raw)
To: pgsql-sql
Hi..
In postgres, you have two options..
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-08-22 06:27 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-13 13:44 Porting from PL/SQL to PLPGSQL Jomon Skariah <jomons@aztec.soft.net>
2003-08-13 19:35 ` Josh Berkus <josh@agliodbs.com>
2003-08-13 19:50 ` Rod Taylor <rbt@rbt.ca>
2003-08-14 08:19 Re: Porting from PL/SQL to PLPGSQL Jomon Skariah <jomons@aztec.soft.net>
2003-08-14 13:46 Porting from PL/SQL to PLPGSQL Jomon Skariah <jomons@aztec.soft.net>
2003-08-20 09:45 Porting from PL/SQL to PLPGSQL Jomon Skariah <jomons@aztec.soft.net>
2003-08-20 21:12 ` Roberto Mello <rmello@cc.usu.edu>
2003-08-22 06:27 ` denis@coralindia.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox