Re: From mailing list: PL/PGSQL returning recordset

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(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco