Thread: Modifying search_path in a stored procedure
I know I can SET the schema search_path in a stored procedure, but is there any way to retrieve it first so that I can get the current value and manipulate that rather than just replace it? I've got two sets of data and two sets of functions in 4 respective schemas. I want to select one set of data and one set of functions. It is fairly straight forward. However, it would be nice if I want to swap the schemas with the functions, I don't have to remember what data schema I was using. Thanks. Eric Brown 408-571-6341 www.propel.com
On Tue, Jan 11, 2005 at 01:19:55PM -0800, Eric Brown wrote: > I know I can SET the schema search_path in a stored procedure, but is > there any way to retrieve it first so that I can get the current value > and manipulate that rather than just replace it? See the current_setting() and set_config() functions in the "Functions and Operators" chapter of the documentation. In 7.3 and 7.4 they're in the "Miscellaneous Functions" section; in 8.0 they're in "System Administration Functions." -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Eric Brown <eric.brown@propel.com> writes: > I know I can SET the schema search_path in a stored procedure, but is > there any way to retrieve it first so that I can get the current value > and manipulate that rather than just replace it? current_setting() would produce the value you want to give back to SET (or more likely set_config()). There's also current_schema() and current_schemas() if you want a more digested form. See "System Information Functions" and "System Administration Functions" in TFM. It might also be that SET LOCAL would solve your problem with less fuss. regards, tom lane