From: Uwe C. Schroeder <uwe@oss4u.com>
To: David.Bear@asu.edu
To: pgsql-admin@postgresql.org
Subject: Re: query and stored procedures
Date: Wed, 5 Jan 2005 18:12:40 -0800
Message-ID: <200501051812.40899.uwe@oss4u.com> (raw)
In-Reply-To: <20050106005024.GK374@asu.edu>
References: <20050106005024.GK374@asu.edu>
-----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-----
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-admin@postgresql.org
Cc: uwe@oss4u.com, David.Bear@asu.edu
Subject: Re: query and stored procedures
In-Reply-To: <200501051812.40899.uwe@oss4u.com>
References: <20050106005024.GK374@asu.edu> <200501051812.40899.uwe@oss4u.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