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

Hi Patrick

at the command line you can use
\i path\myfunction.sql (as mentioned by Richard Broersma Jr)

if postgres compiles the function when you call "create function", I
don't know, but I guess it does.

if there are errors they show on the command line as well.

Patrick Ng schrieb:
> 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
you call the "create function" statement once, then you use the select
statement to call the new function.

simple expl.:
CREATE function func_test(integer)
returns integer
as
$BODY$

select $1*2;

$BODY$
LANGUAGE 'SQL';

SELECT func_test(34);
SELECT func_test(12);
> 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.
>
>