pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Gerald Gutierrez <pozix@home.com>
To: sqllist <pgsql-sql@postgresql.org>
Subject: Re: From mailing list: PL/PGSQL returning recordset
Date: Sun, 04 Mar 2001 18:04:56 -0800
Message-ID: <3AA2F448.7464894E@agliodbs.com> (raw)
References: <5.0.2.1.0.20010302095340.0299c680@mail.rchmd1.bc.wave.home.com>

Gerald,

> I was searching for a way to return a recordset from PL/PGSQL (to implement
> Dijkstra's algorithm) when I came upon your post at:

> You mentioned that you posted a "workaround", but I've been unable to find
> it with much searching. I may not be looking in the right place. Can you
> please refer me to the correct place, or send me the post if you have it handy?

Here's the method we're using for all of our searches in our current
application.  Pardon my not supplying full SQL declarations; if I had
to, I'd never write this e-mail.

1. Assume that you have a table "staff" with primary key staff_id.

2. Create a second table, "searches" with two columns:  search_key and
ref_id, both NOT NULL and INT4, and unique in combination.

3. Create a sequence search_sq.

4. Write a function to search the staff table (and related sub-tables)
based on 6 different criteria (name matching, staff role, SSN, etc.)
singly or in combination.

5. The above function will, after searching the staff table, insert the
appropriate staff_id's into the searches table together with a unique
search_key obtained through NEXTVAL('search_sq').  After the insert, the
function will return the search_key as its result (with 0 indicating
error/no records).

6. You may then display records from the staff table based on linking it
with the searches table, without fear that two user's searches will get
mixed up.  Futher, in a low-transaction situation, the searches table
may be used for all searches in the DB on tables with INT4 primary keys.

-Josh Berkus


-- 
______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology      josh@agliodbs.com
    and data management solutions       (415) 565-7293
   for law firms, small businesses       fax  621-2533
    and non-profit organizations.       San Francisco



Message-ID: <3AA2F448.7464894E@agliodbs.com>
Permalink:  ../3AA2F448.7464894E@agliodbs.com/
Also on:    postgresql.org/message-id/3AA2F448.7464894E@agliodbs.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: josh@agliodbs.com, pozix@home.com
  Subject: Re: From mailing list: PL/PGSQL returning recordset
  In-Reply-To: <3AA2F448.7464894E@agliodbs.com>
  References: <5.0.2.1.0.20010302095340.0299c680@mail.rchmd1.bc.wave.home.com> <3AA2F448.7464894E@agliodbs.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox