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

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

From: Christoph Della Valle <christoph(dot)dellavalle(at)goetheanum(dot)ch>
To: Patrick Ng <patrick(dot)ng(at)zuji(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: RE : How do I compile/test a PL/SQL in Postgresql
Date: 2006-07-18 07:06:20
Message-ID: 44BC886C.7020505@goetheanum.ch (view raw or flat)
Thread:
Lists: pgsql-novice
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.
> 
> 

In response to

pgsql-novice by date

Next:From: Slavisa GaricDate: 2006-07-18 10:22:35
Subject: Disk space taken
Previous:From: Christopher Kings-LynneDate: 2006-07-18 01:39:28
Subject: Re: [NOVICE] The name of the game

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