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: "Richard Broersma Jr" <rabroersma(at)yahoo(dot)com>,<pgsql-novice(at)postgresql(dot)org>
Subject: Re: RE : How do I compile/test a PL/SQL in Postgresql
Date: 2006-07-20 13:24:20
Message-ID: 631AA2476DFFD047873427369B8C06980332981C@teasgex1.teasin.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi,

I login as :

psql -U abc -d DB_NAME

At the Postgresql prompt, I type \i d:\abc.sql

but got a D:: Permission denied

I have added MACHINE_NAME\postgres user to d: drive and its subfolders
but am still getting the above error.

Do you have any idea what can be wrong? abc.sql is a stored function.

Best regards

-----Original Message-----
From: Richard Broersma Jr [mailto:rabroersma(at)yahoo(dot)com] 
Sent: Monday, July 17, 2006 8:17 PM
To: Patrick Ng; pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] RE : How do I compile/test a PL/SQL in Postgresql

> 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 

I don't know if you've seen this link, but I should be useful.
http://www.postgresql.org/docs/8.1/interactive/plpgsql-porting.html

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

http://www.postgresql.org/files/documentation/books/aw_pgsql/node143.htm
l#SECTION002411000000000000000

I would do:

psql-> \i <file-path>\function.sql


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

I believe that:

psql-> \o filename

or from the command line you can get query results returned to standard
out.  And then you could
"pipe" the result to whatever you wanted.

$ psql -u <user> -d <mydb> -c "select * from test" | grep -e "hello
world" > hello.txt 



> In PostgreSQL, how do I test the stored function? I noticed none of
the
> documentation or books seemed to mention this simple point.

Well, I would run it to see if it was syntactically correct.
Then I would check to see if the results were as I expected.
Next I would execute the function using "explain analyze" to see if
there are any preformance
issues that need to be resolved.



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

Responses

pgsql-novice by date

Next:From: Richard Broersma JrDate: 2006-07-20 13:47:31
Subject: Re: RE : How do I compile/test a PL/SQL in Postgresql
Previous:From: Richard Broersma JrDate: 2006-07-19 21:38:25
Subject: Re: Updating of serial ID fields

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