Skip site navigation (1) Skip section navigation (2)

Re: query and stored procedures

From: "Uwe C(dot) Schroeder" <uwe(at)oss4u(dot)com>
To: David(dot)Bear(at)asu(dot)edu, pgsql-admin(at)postgresql(dot)org
Subject: Re: query and stored procedures
Date: 2005-01-06 02:12:40
Message-ID: 200501051812.40899.uwe@oss4u.com (view raw or flat)
Thread:
Lists: pgsql-admin
-----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-----


In response to

pgsql-admin by date

Next:From: Joshua D. DrakeDate: 2005-01-06 03:14:22
Subject: Re: query and stored procedures
Previous:From: David BearDate: 2005-01-06 00:50:24
Subject: query and stored procedures

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group