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: (view raw or whole thread)
Lists: pgsql-novice

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

I don't know if you've seen this link, but I should be useful.

> do this at SQL*PLUS prompt : @<file-path\file_name to compile the
> function into ORACLE DB.  
> In PostgreSQL, how do I do that?

I would do:

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

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

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


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-2015 The PostgreSQL Global Development Group