Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2525Mx01671 for ; Sun, 4 Mar 2001 21:05:22 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [209.122.114.86] (HELO agliodbs.com) by davinci.ethosmedia.com (CommuniGate Pro SMTP 3.4.1) with ESMTP id 20256; Sun, 04 Mar 2001 18:05:19 -0800 Message-ID: <3AA2F448.7464894E@agliodbs.com> Date: Sun, 04 Mar 2001 18:04:56 -0800 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions X-Mailer: Mozilla 4.73 [en] (Win98; I) X-Accept-Language: en MIME-Version: 1.0 To: Gerald Gutierrez , sqllist Subject: Re: From mailing list: PL/PGSQL returning recordset References: <5.0.2.1.0.20010302095340.0299c680@mail.rchmd1.bc.wave.home.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/45 X-Sequence-Number: 1690 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