×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

stored procedures

stored procedures

stored procedures

(OP)
i want a stored procedure that returns more than one value.

eg. a stored procedure that takes a username and returns set of jobs that belong to that user.

i've seen the type "RECORD" but how the heck can i return the RECORD or is this something completely different?

is this kind of thing even possible in postgresql??

RE: stored procedures

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)
https://www.smvfp.com
Nothing Runs Like the Fox

RE: stored procedures

(OP)
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?

RE: stored procedures

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)
https://www.smvfp.com
Nothing Runs Like the Fox

RE: stored procedures

(OP)
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.

RE: stored procedures

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:

    https://www.smvfp.com

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)
https://www.smvfp.com
Nothing Runs Like the Fox

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close