stored procedures 1

Hi,

You could have two tables. One would contain the user name. The other could contain jobs. In the first table each user should be assign a unique number that would become the primary key for the first table. Primary key automatically have an index associated with them. I would also have a foreign key in the second table that referenced back to the first table base on the unique username number. Each row in the jobs table should contain a username number that reference back to the primary table. When you set up the foreign key in the second table, postgres will automatically build a contraint trigger to enforce referential integrity between the two tables. Then it should be fast and easy to select job rows in the second table that belong to a particular username number.
Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox

sorry, i was worried about how to make my stored procs return multiple values.
part of my problem is that i'm not sure what it's called, i just want a proc that does a

select * from.....

so i can just call the proc quickly in java without having to write long sql statements. the thread on cursors may be something but again i'm not sure

your answer did wake me up a bit tho as i had completely forgotten about foreign keys (my sql is rusty).

is it me or are the postgres docs awful tho?

Hi MrTom,

I would be nice if the postgres doc were better, but I think that could be said of about any application. What are using as a font-end to talk to postgres server? Are you using something like PgAccess II or phpPgAdmin, or some language like C++, perl, or are you using the postgres psql prompt? Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox

i tried both pgaccessII and phppgadmin. pgaccess is ok, but is a little annoying. at the moment i'm just using phppgadmin but manually entering my stored procs as the helper doesn't seem to be able to do everything.
i think i'll get the oreilly book as it seems to cover pretty much everything.

Hi MrTom,

Getting the book is a good idea. I've never done a stored procedure in postgres, so I can't be of much help on that. However, if you go to the postgres web site, they have serveral mailing lists. You should consider joining several of these as it would allow you to learn from asking questions and sitting in on conversations where others are asking questions.

You might consider using a front-end tool that would allow you to program SQL to talk to the postgres database server. I have written an example app using a Visual FoxPro 7 front-end client that talks to a postgres back-end server, but there are many programming languages that can be used. If your interested you might take a look as the example app to see if there are any ideas in it you could use. My site is at:


After entering the sight click on option 18. There is a protential problem. You will need a copy of VFP7 in order to run the example app.

Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox