Oracle PL/SQL Anonymous block equivalent in postgres

Ashok Agrawal wrote:

Hi ,

Here is the requirements :

1. Development & Support team can ONLY have READ Only ACCESS
to PRODUCTION Database. They will NOT have access to create
stored procedure & functions in the PRODUCTION on the fly.

2. During application support, need to write script which
uses procedural language (IF ELSE, AND , OR, Cursor, Loop etc)
to produce results or identify/fix issues. Number of this kind of
Requests goes in hundreds during Quarter end.

Currently on Oracle as long as you have sqlplus read only access,
you can write PL/SQL block and get the work done.

Since postgres doesn't support procedural language except in
stored objects like procedure/functions, how do I achieve in
postgres without using stored objects. If this is not possible,
then does it make sense to add this requirement into TO-DO list.

So you want to write procedural code on the client. psql works but
doesn't have IF and loop constructs. pgbash allows this, though you are
writing shell scripts. Is that OK? Here are some examples:

http://www.psn.co.jp/PostgreSQL/pgbash/example-e.html

You can do things in perl too, if you want, and tcl, and almost any
other open source language.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Ühel kenal päeval, N, 2005-12-22 kell 18:19, kirjutas Bruce Momjian:

Ashok Agrawal wrote:

Hi ,

Here is the requirements :

1. Development & Support team can ONLY have READ Only ACCESS
to PRODUCTION Database. They will NOT have access to create
stored procedure & functions in the PRODUCTION on the fly.

2. During application support, need to write script which
uses procedural language (IF ELSE, AND , OR, Cursor, Loop etc)
to produce results or identify/fix issues. Number of this kind of
Requests goes in hundreds during Quarter end.

Currently on Oracle as long as you have sqlplus read only access,
you can write PL/SQL block and get the work done.

Since postgres doesn't support procedural language except in
stored objects like procedure/functions, how do I achieve in
postgres without using stored objects. If this is not possible,
then does it make sense to add this requirement into TO-DO list.

So you want to write procedural code on the client.

I guess he rather wants to have dont-save-but-execute-immediately
pl/pgsql code.

could this perhaps be solved by having temporary functions, similar to
postgresql's temporary tables ?

--------------
Hannu

Hannu Krosing wrote:

?hel kenal p?eval, N, 2005-12-22 kell 18:19, kirjutas Bruce Momjian:

Ashok Agrawal wrote:

Hi ,

Here is the requirements :

1. Development & Support team can ONLY have READ Only ACCESS
to PRODUCTION Database. They will NOT have access to create
stored procedure & functions in the PRODUCTION on the fly.

2. During application support, need to write script which
uses procedural language (IF ELSE, AND , OR, Cursor, Loop etc)
to produce results or identify/fix issues. Number of this kind of
Requests goes in hundreds during Quarter end.

Currently on Oracle as long as you have sqlplus read only access,
you can write PL/SQL block and get the work done.

Since postgres doesn't support procedural language except in
stored objects like procedure/functions, how do I achieve in
postgres without using stored objects. If this is not possible,
then does it make sense to add this requirement into TO-DO list.

So you want to write procedural code on the client.

I guess he rather wants to have dont-save-but-execute-immediately
pl/pgsql code.

could this perhaps be solved by having temporary functions, similar to
postgresql's temporary tables ?

I am thinking they want the ability to sit at a prompt and type stuff.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

I don't need ability to sit at a prompt and type stuff.

To be more clear, I am attaching one sample code. I would like to
migrate this code using postgres without converting into procedure
or function.

Thanks
Ashok

Bruce Momjian wrote On 12/22/05 15:35,:

Hannu Krosing wrote:

?hel kenal p?eval, N, 2005-12-22 kell 18:19, kirjutas Bruce Momjian:

Ashok Agrawal wrote:

Hi ,

Here is the requirements :

1. Development & Support team can ONLY have READ Only ACCESS
to PRODUCTION Database. They will NOT have access to create
stored procedure & functions in the PRODUCTION on the fly.

2. During application support, need to write script which
uses procedural language (IF ELSE, AND , OR, Cursor, Loop etc)
to produce results or identify/fix issues. Number of this kind of
Requests goes in hundreds during Quarter end.

Currently on Oracle as long as you have sqlplus read only access,
you can write PL/SQL block and get the work done.

Since postgres doesn't support procedural language except in
stored objects like procedure/functions, how do I achieve in
postgres without using stored objects. If this is not possible,
then does it make sense to add this requirement into TO-DO list.

So you want to write procedural code on the client.

I guess he rather wants to have dont-save-but-execute-immediately
pl/pgsql code.

could this perhaps be solved by having temporary functions, similar to
postgresql's temporary tables ?

I am thinking they want the ability to sit at a prompt and type stuff.

~

Wow, that is large. I think PL/pgSQL is your best approach. I
recommend you create a schema that users can write into.

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

Ashok Agrawal wrote:

I don't need ability to sit at a prompt and type stuff.

To be more clear, I am attaching one sample code. I would like to
migrate this code using postgres without converting into procedure
or function.

Thanks
Ashok

Bruce Momjian wrote On 12/22/05 15:35,:

Hannu Krosing wrote:

?hel kenal p?eval, N, 2005-12-22 kell 18:19, kirjutas Bruce Momjian:

Ashok Agrawal wrote:

Hi ,

Here is the requirements :

1. Development & Support team can ONLY have READ Only ACCESS
to PRODUCTION Database. They will NOT have access to create
stored procedure & functions in the PRODUCTION on the fly.

2. During application support, need to write script which
uses procedural language (IF ELSE, AND , OR, Cursor, Loop etc)
to produce results or identify/fix issues. Number of this kind of
Requests goes in hundreds during Quarter end.

Currently on Oracle as long as you have sqlplus read only access,
you can write PL/SQL block and get the work done.

Since postgres doesn't support procedural language except in
stored objects like procedure/functions, how do I achieve in
postgres without using stored objects. If this is not possible,
then does it make sense to add this requirement into TO-DO list.

So you want to write procedural code on the client.

I guess he rather wants to have dont-save-but-execute-immediately
pl/pgsql code.

could this perhaps be solved by having temporary functions, similar to
postgresql's temporary tables ?

I am thinking they want the ability to sit at a prompt and type stuff.

~

[ application/x-soffice is not supported, skipping... ]

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

I know this is a tech forum and as such, I don't generally plug products too
much. However, EnterpriseDB has anonymous PL/SQL if you need it.

Jonah H. Harris wrote:

I know this is a tech forum and as such, I don't generally plug products too
much. However, EnterpriseDB has anonymous PL/SQL if you need it.

Interesting. The posting is certainly appropriate. I know Command
Prompt and GreenPlum have been using this "loophole". :-)

In fact, I am wondering how you did that. It is inside psql?

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

On 12/22/05, Bruce Momjian <pgman@candle.pha.pa.us> wrote:

Wow, that is large. I think PL/pgSQL is your best approach. I
recommend you create a schema that users can write into.

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

Ashok Agrawal wrote:

I don't need ability to sit at a prompt and type stuff.

To be more clear, I am attaching one sample code. I would like to
migrate this code using postgres without converting into procedure
or function.

Thanks
Ashok

Bruce Momjian wrote On 12/22/05 15:35,:

Hannu Krosing wrote:

?hel kenal p?eval, N, 2005-12-22 kell 18:19, kirjutas Bruce Momjian:

Ashok Agrawal wrote:

Hi ,

Here is the requirements :

1. Development & Support team can ONLY have READ Only ACCESS
to PRODUCTION Database. They will NOT have access to create
stored procedure & functions in the PRODUCTION on the fly.

2. During application support, need to write script which
uses procedural language (IF ELSE, AND , OR, Cursor, Loop etc)
to produce results or identify/fix issues. Number of this kind of
Requests goes in hundreds during Quarter end.

Currently on Oracle as long as you have sqlplus read only access,
you can write PL/SQL block and get the work done.

Since postgres doesn't support procedural language except in
stored objects like procedure/functions, how do I achieve in
postgres without using stored objects. If this is not possible,
then does it make sense to add this requirement into TO-DO list.

So you want to write procedural code on the client.

I guess he rather wants to have dont-save-but-execute-immediately
pl/pgsql code.

could this perhaps be solved by having temporary functions, similar to
postgresql's temporary tables ?

I am thinking they want the ability to sit at a prompt and type stuff.

~

[ application/x-soffice is not supported, skipping... ]

--
Bruce Momjian                        |  http://candle.pha.pa.us
pgman@candle.pha.pa.us               |  (610) 359-1001
+  If your life is a hard drive,     |  13 Roberts Road
+  Christ can be your backup.        |  Newtown Square, Pennsylvania
19073

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

It's implemented in the server with extensions for psql to support it.

On Thu, Dec 22, 2005 at 06:35:24PM -0500, Bruce Momjian wrote:

Hannu Krosing wrote:

?hel kenal p?eval, N, 2005-12-22 kell 18:19, kirjutas Bruce Momjian:
I guess he rather wants to have dont-save-but-execute-immediately
pl/pgsql code.

could this perhaps be solved by having temporary functions, similar to
postgresql's temporary tables ?

I am thinking they want the ability to sit at a prompt and type stuff.

FWIW, I would find the ability to run stuff from the prompt useful for
debugging plpgsql functions. Rather than

edit function def
CREATE OR REPLACE FUNCTION ...
test function

you could just edit in-place and test.

Of course having the ability to execute arbitrary plpgsql in .sql
scripts would be handy in some cases as well, though as others pointed
out there are alternatives.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Of course having the ability to execute arbitrary plpgsql in .sql
scripts would be handy in some cases as well, though as others pointed
out there are alternatives.

I don't know if it's possible. PL/pgSQL parser live in different context
than SQL parser and PostgreSQL supports more languages than one. These needs
different syntax, maybe:

EXECUTE CODE (integer, integer) RETURNS integer AS $$
DECLARE c integer = 10;
BEGIN
RETURN $1 + $2 + c;
END;
$$ LANGUAGE plppgsql USING(10,20);

we can use plplgsql compiler, and only use different memory context. It's
maybe better solution than temp functions.

Regards
Pavel Stehule

_________________________________________________________________
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/