Обсуждение: pl/pgSQL development environment
I have to convert a large number of Oracle pl/sql scripts to PostgreSQL
8.3 pl/pgSQL and
I am stuck on the most basic part, how to do development with pl/pgSQL.
First of all what is the development model for pl/pgSQL development, i.e.
What is the 'industry standard' way edit, compile and test functions
and procedures?
I don't see any examples of a complete process in the documentation.
I created the following simple function as a first try:
CREATE OR REPLACE FUNCTION testfunc() RETURNS integer AS $$
BEGIN
RAISE DEBUG 'Output from testfunc';
return 2 ;
END ;
$$ LANGUAGE plpgsql;
Using both psql and pgADMINIII I was able to load and execute this and
see 2 as the output but I did not
see the output of the RAISE statement. The RAISE output was not in the
log file either.
Where does it go and can it be redirected to stdout?
Since pl/pgSQL does not have packages, what is the usual way of managing
groups of related functions and procedures. For example I have
a group pl/sql procedures that call functions from a library. The
library is one package. The procedures that
use the library are in separate packages.
Thanks for any help and/or guidance,
Bill Stafford
On Mon, Aug 4, 2008 at 12:40 PM, Wm.A.Stafford <stafford@marine.rutgers.edu> wrote: > Using both psql and pgADMINIII I was able to load and execute this and see > 2 as the output but I did not > see the output of the RAISE statement. The RAISE output was not in the log > file either. > Where does it go and can it be redirected to stdout? You have raised many good questions. This is one that I can answer. To see "DEBUG' level logging, you have to alter your Postgresql.conf file to specify that DEBUG level of logging be written to the log files. If you were to raise EXECEPTION or NOTICE instead, depending upon your log level verbosity, you will most likely see this level of detail written to your logs. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Regarding the question about packages from oracle and the PG functions implementation. PG does not have the package conceptor library of pl/pgsql functions. It just implements functions and they can be separated by user's different objets or different schema's objets. You have this in PG so far, and it works pretty well in terms of operational uses. Maybe speaking about performance and organizationsissues, it has to grow much more yet. Regards, Charles --- El lun, 4/8/08, Wm.A.Stafford <stafford@marine.rutgers.edu> escribió: > De: Wm.A.Stafford <stafford@marine.rutgers.edu> > Asunto: [NOVICE] pl/pgSQL development environment > Para: pgsql-novice@postgresql.org > Fecha: lunes, 4 agosto, 2008 3:40 > I have to convert a large number of Oracle pl/sql scripts to > PostgreSQL > 8.3 pl/pgSQL and > I am stuck on the most basic part, how to do development > with pl/pgSQL. > > First of all what is the development model for pl/pgSQL > development, i.e. > What is the 'industry standard' way edit, compile > and test functions > and procedures? > I don't see any examples of a complete process in the > documentation. > > I created the following simple function as a first try: > CREATE OR REPLACE FUNCTION testfunc() RETURNS integer AS $$ > BEGIN > RAISE DEBUG 'Output from testfunc'; > return 2 ; > END ; > $$ LANGUAGE plpgsql; > > Using both psql and pgADMINIII I was able to load and > execute this and > see 2 as the output but I did not > see the output of the RAISE statement. The RAISE output > was not in the > log file either. > Where does it go and can it be redirected to stdout? > > Since pl/pgSQL does not have packages, what is the usual > way of managing > groups of related functions and procedures. For example I > have > a group pl/sql procedures that call functions from a > library. The > library is one package. The procedures that > use the library are in separate packages. > > Thanks for any help and/or guidance, > Bill Stafford > > -- > Sent via pgsql-novice mailing list > (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice ______________________________________________ Enviado desde Correo Yahoo! La bandeja de entrada más inteligente.
"Wm.A.Stafford" <stafford@marine.rutgers.edu> writes:
> ... Using both psql and pgADMINIII I was able to load and execute this and
> see 2 as the output but I did not
> see the output of the RAISE statement. The RAISE output was not in the
> log file either.
> Where does it go and can it be redirected to stdout?
In addition to the other comments: client_min_messages can be adjusted
if you want to get DEBUG-level messages on the client side. (I'm not
sure how well that works in pgAdmin, but it definitely works in psql.)
Realize though that you'll get chatter from built-in DEBUG output too,
whether you are sending it to the log or the client. It might be better
to use a higher message level for temporary debugging messages in
plpgsql functions. I tend to use NOTICE myself.
> Since pl/pgSQL does not have packages, what is the usual way of managing
> groups of related functions and procedures.
About all you can do at the moment is put them all into a schema.
There has been some talk of creating a "module" facility in future
PG releases, but nothing's happened beyond talk, yet.
regards, tom lane