Thread: Oracle PL/SQL Anonymous block equivalent in postgres

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.

Pls advice.

Thanks
Ashok

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,
Pennsylvania19073
 

Ü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,
Pennsylvania19073
 

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,
Pennsylvania19073
 

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,
Pennsylvania19073
 

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/