Re: RE : How do I compile/test a PL/SQL in Postgresql

Hi Christoph,

Thanks for the quick reply. I have placed my PL/SQL into a file. So at
command line (after logging in using psql and getting the Postgresql
prompt), how do I run the entire PL/SQL in the file?

So I gather there is no compilation of PL/SQL under PostgreSQL (unlike
ORACLE). One would just have to call the PL/SQL using SELECT statement
and if it works, it means its OK? If it hits an error, how do I get
error codes out? ORACLE PL/SQL can return error codes via SQLERRM and
SQLCODE variables accessible within the PL/SQL? Does PostgreSQL also
return the same error codes via accessible variables?

Thank you & best regards

-----Original Message-----
From: Christoph Della Valle [mailto:christoph(dot)dellavalle(at)goetheanum(dot)ch]
Sent: Monday, July 17, 2006 6:13 PM
To: Patrick Ng
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] RE : How do I compile/test a PL/SQL in Postgresql

Hi

you can do it at the command line:
open a shell, change to your dbuser, then
psql mydb
or psql mydb -u USER -h HOST

Or install pgAdminIII, a common GUI for postgres (I prefer the
commandline.)
If you use the commandline, make sure readline-support is installed.

If you run (on the shell) your "CREATE OR REPLACE FUNCTION"-Statement,
that's it. After this, you call your new function like this:

select myfunc(arg);

since functions are polymorphic, you have to use the appropriate amount
of arguments, otherwise you will get the message that this function does
not exist...

yours,
Christoph

Patrick Ng schrieb:
> Hi,
>
>
>
> I am a novice to PostgreSQL (although I know ORACLE's PL/SQL very
well)
>
>
>
> I have written a Stored Function in PostgreSQL but cannot figure out
how
> to compile it or run it in PostgreSQL. In ORACLE, one would have to
>
> do this at SQL*PLUS prompt : @<file-path\file_name to compile the
stored
> function into ORACLE DB.
>
> In PostgreSQL, how do I do that?
>
>
>
> In ORACLE, one would have to write a PL/SQL to test the stored
function
> (and use DBMS_OUTPUT.PUT_LINE) to get the stored function to write to
> stdout.
>
>
>
> In PostgreSQL, how do I test the stored function? I noticed none of
the
> documentation or books seemed to mention this simple point.
>
>
>
> Best regards
>
>
>
>

________________________________________________________________________
This email has been scanned for all viruses by the MessageLabs Email
Security System.