For SQL, check out http://www.sqlcourse.com/. As for tutorials with PG.pm, all I can find seem to be in Italian. Unless you speak it, that's probably not terribly useful.

Why do you say that PG.pm is "the only thing that works on [your] machine"? Why doesn't DBI work? And is it really the case that nothing else works on your machine? How have you verified this? If you're going to work with PG.pm and you're not terribly familiar with Perl (as your post suggest to me), then you're going to have to face the fact that lesser-known technologies are going to be more difficult to get assistance with. Taking the time to figure out how to get DBI to work will reap great benefits in the long run. More people can help you and if you need to switch databases, it's a snap.

You can learn more about DBI with this short introduction.

Cheers,
Ovid

Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

P.S. This site isn't a a "pirated" version of the text. It's legit. See the notice from Command Prompt.

-Blake

Thanks for the links. I will be using them for sure. In regards to the DBI issue I have twice tried to get it working (with DBD). Other perl monks have been stumped at the issues I came up with (if I knew how to reference other nodes I could show you). I figure that since I have the Pg working I will run with it and see what I get. I will be needing to redo the code regardless after to optimize it later (this is my first database project and will certainly be less than optimal).

Thanx for the info.
D.

Maybe you installed Postgres from a package, such as an RPM file, and the maintainer neglected to include the documentation tarball. Know that there is a large pile of documentation that comes with Postgres, and you can even download it as a separate component from a mirror site near you. For memory there is some tutorial-type material provided.

g r i n d e r
just another bofh

print@_{sort keys %_},$/if%_=split//,'= & *a?b:e\f/h^h!j+n,o@o;r$s-t%t#u';

I also recently had an opportunity to use postgres with perl on linux. I got "Begining Databases with PostgreSQL" isbn: 1-861005-15-5 from Amazon.

I'm pretty fluent with oracle and RDBMS's in general, and found this book covered lots of what I already knew. It covers from basic SQL to advanced things like correlated sub-queries, and also bits on Admin of Postgres

It also covers design aspects fo RDBMS's, as well as stored proc's, triggers, transactions, locking, DDL and DML. It sounds like it may be a good catch for you. There is also a nifty section on embeding perl, php, java and c in your database.

As for the install of Postgres, i did it myself (explicitly didnt install it from the Red-Hat CD's I have), and it worked fine 1st time. If you intend imbedding some perl in your database, make sure you compile perl into it when you build your Postgres DB.

One tip for DB design, the owner of the objects (tables, indexes et al) should not be the user that access the objects in production. For example you may have a Postgres user called Webmgr that you would use to create all the tables for a web application, then you would have another Postgres user called Apache, which would do the reading and writing from the tables. The distinct difference between the two users is your Webmgr user has the ability to drop tables, and alter the structure of your schema, while the Apache user only has the permissions to do what your application requires (usually only select, insert, update and delete, or a subset thereof)

As for perl hooks into it, I would strongly recommend using DBI with the Pg driver. Its really very simple to install, altho' you'll have to download the Pg driver seperately from the DBI module. Everyone knows DBI, there is tonnes of help available online, and O'Reilly have a really cool book on using it.

I'm using this combination (perl + postgres) for the infrastructure of a web application, and it couldnt be easier.

Not sure if this is the case with the PG.pm driver you're currently using, but you can also port your applications to different databases almost transparently if one day you suddenly find you must now use an Oracle database. all my oracle coding i've done in perl i've had to make almost zero changes to in order to port it across. the changes i've had to make are pretty much SQL ones that use Oracle "hints", that are not SQL-92 complient

Good luck on your adventure with perl and postgres, I've learnt heaps from it, I'm sure you will also.

Besides buying it will help pay for his next date. :)

mitd-Made in the Dark
'Interactive! Paper tape is interactive!
If you don't believe me I can show you my paper cut scars!'

I have postgres running in 2 locations
One is local for developing and testing purposes
The other is used as a web server.
I get the above error on the web server even though I have a postgres user set up. I will check the environmental variables but I am pretty sure they are set. Since I can use createuser what do I need to do to specify a password to connect with (using a shell not a script)?

Hope my question makes sense.
D.

-- Brett

Go not to the Elves for counsel, for they will say both no and yes

I really appreciate all the advice but I must be moving the project along. I realize that PG.pm is not very portable but as I said before I am new and will certainly need to rewrite some of my source code anyway. This is an educational project that address databases and the web.

Since I have you all here perhaps someone can help me with this:

I was logged in as root and tried to create a new user for my Postgres database and recieved this:

# createuser mydatabase
Connection to database 'template1' failed.
fe_sendauth: no password supplied

Can anyone help me with this error. On my local machine I can create users without hassle.

Thanx, D.

Create a "postgres" user, and set up all the environment variables such as PATH, and LD_LIBRARY_PATH, then run createuser this way.

Make sure postmaster is not running as root. In fact i think postgres checks to see if a root account is trying to run it, and wont allow it.

It's pretty easy to whip up a script to execute postgres at startup as well, and saves starting it manually each time.

If youre using it for a web application, can i also recommend using the CGI::Application/HTML::Template framework. It provides a nice structur for the applications you write. I guess down the track you'd want to look at mod_perl as well. It loads everything into memory, so your script only has to load once, not every time its called.