Skip site navigation (1) Skip section navigation (2)

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

From: "Patrick Ng" <patrick(dot)ng(at)zuji(dot)com>
To: "Christoph Della Valle" <christoph(dot)dellavalle(at)goetheanum(dot)ch>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: RE : How do I compile/test a PL/SQL in Postgresql
Date: 2006-07-18 01:10:39
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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


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
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...


Patrick Ng schrieb:
> Hi,
> I am a novice to PostgreSQL (although I know ORACLE's PL/SQL very
> I have written a Stored Function in PostgreSQL but cannot figure out
> 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
> 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
> (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
> 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.


pgsql-novice by date

Next:From: Christopher Kings-LynneDate: 2006-07-18 01:39:28
Subject: Re: [NOVICE] The name of the game
Previous:From: Ned LillyDate: 2006-07-17 21:18:06
Subject: Re: [NOVICE] The name of the game

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group