| Lists: | pgsql-general |
|---|
| From: | Eric Brown <eric(dot)brown(at)propel(dot)com> |
|---|---|
| To: | Pgsql-General (E-mail) <pgsql-general(at)postgresql(dot)org> |
| Subject: | Modifying search_path in a stored procedure |
| Date: | 2005-01-11 21:19:55 |
| Message-ID: | 89F0EA2B-6416-11D9-94E2-000A95C7176C@propel.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
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
| From: | Michael Fuhr <mike(at)fuhr(dot)org> |
|---|---|
| To: | Eric Brown <eric(dot)brown(at)propel(dot)com> |
| Cc: | Pgsql-General <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Modifying search_path in a stored procedure |
| Date: | 2005-01-11 21:55:54 |
| Message-ID: | 20050111215554.GA43087@winnie.fuhr.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
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/
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Eric Brown <eric(dot)brown(at)propel(dot)com> |
| Cc: | Pgsql-General (E-mail) <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Modifying search_path in a stored procedure |
| Date: | 2005-01-11 22:46:43 |
| Message-ID: | 4822.1105483603@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
Eric Brown <eric(dot)brown(at)propel(dot)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