X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0F3663A4373 for ; Thu, 6 Jan 2005 02:17:43 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 07799-01 for ; Thu, 6 Jan 2005 02:17:22 +0000 (GMT) Received: from www.postgresql.com (unknown [200.46.204.209]) by svr1.postgresql.org (Postfix) with ESMTP id 6C0E33A43DC for ; Thu, 6 Jan 2005 02:17:15 +0000 (GMT) Received: from deep.parasun.net (deep.mindlink.net [204.174.16.4]) by www.postgresql.com (Postfix) with ESMTP id F2C32739CD5 for ; Thu, 6 Jan 2005 02:16:29 +0000 (GMT) Received: from pop.sanbrunocable.com ([65.172.230.120]) by deep.parasun.net with esmtp (Exim 4.43) id 1CmN8k-0003KA-7t for pgsql-admin@postgresql.org; Wed, 05 Jan 2005 18:12:42 -0800 Received: from oss4u.com (cpe-65-172-234-54.sanbrunocable.com [65.172.234.54]) by pop.sanbrunocable.com (8.11.6/8.11.6) with SMTP id j062Awj26598 for ; Wed, 5 Jan 2005 18:10:59 -0800 Received: (qmail 10387 invoked from network); 6 Jan 2005 02:07:53 -0000 Received: from tiburon.oss4u.com (192.168.1.4) by gw.oss4u.com (192.168.1.1) with ESMTP; 06 Jan 2005 02:07:53 -0000 From: "Uwe C. Schroeder" Organization: OSS4U, LLC To: David.Bear@asu.edu, pgsql-admin@postgresql.org Subject: Re: query and stored procedures Date: Wed, 5 Jan 2005 18:12:40 -0800 User-Agent: KMail/1.5.3 References: <20050106005024.GK374@asu.edu> In-Reply-To: <20050106005024.GK374@asu.edu> MIME-Version: 1.0 Content-Type: Text/Plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Description: clearsigned data Content-Disposition: inline Message-Id: <200501051812.40899.uwe@oss4u.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.205 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200501/32 X-Sequence-Number: 15891 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Well, I'd use pgsql - as a quick idea you could use something like: CREATE OR REPLACE FUNCTION checkssn(varchar) RETURNS SETOF whatever_your_table AS ' DECLARE incoming_ssn ALIAS FOR $1; result whatever_your_table%ROWTYPE; my_internal_id VARCHAR; BEGIN -- check if this is a ssn or internal id SELECT INTO my_internal_id internal_id FROM whatever_your_table WHERE internal_id=incoming_ssn; IF NOT FOUND THEN -- probably a ssn, so the above doesnt hit anything SELECT INTO my_internal_id internal_id FROM other_table WHERE ssn=incoming_ssn; IF NOT FOUND THEN RAISE EXCEPTION ''SSN not found!''; END IF; END IF; -- since this one returns SETOF you have to loop over the result, even if its just one FOR result IN SELECT * FROM whatever_your_table WHERE internal_id=my_internal_id LOOP RETURN NEXT result; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql' VOLATILE; Hope that helps UC On Wednesday 05 January 2005 04:50 pm, David Bear wrote: > I'm so totally new to postgresql that I'm not sure how to word this. > > I want to create a query, that does two things > > 1) take input in the form of a string of numbers, and test if > a) is ssn > b) or internal id > > if the string is an ssn, make a second query to another data base, > asking it to convert the ssn to our internal id > > otherwise, > > 2) use the id given to query an existing table > > I'm not sure what feature/function postgresql has to help me do this. > I'd like to stick with using python as my language, and I'm guessing > this would be stored procedure, but I'm too new to know. > > Please point me to some good reading. - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax: +1 650 872 2417 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFB3J6YjqGXBvRToM4RAi1MAJ4oNUtL1AXq90c5OCYvcECAn22O9ACgshMB jT19mmCz4nHRbCjy07/wCq0= =DVA8 -----END PGP SIGNATURE-----