Postgres Pro
Downloads
Home   >   mailing lists

Thread: Quoting $user as Parameter to SET

Quoting $user as Parameter to SET

From
"Thomas F. O'Connell"
Date:
The default search_path is $user, public.

Say I wanted to reverse this in a database called d:

First of all:

d=> show search_path;
 search_path
--------------
 $user,public
(1 row)

Then:

d=> alter database d set search_path to public, '$user';

Then, in a new session:

d=> show search_path;
 search_path
-----------------
 public, "$user"
(1 row)

This is an important distinction because testing reveals that the quoted $user after the reversal is no longer actually a dynamic variable that results in a search_path that resolves to the current user.

I'm having trouble locating an example of how to include a variable in the parameter list to SET.

--

Thomas F. O'Connell

Co-Founder, Information Architect

Sitening, LLC


Strategic Open Source: Open Your i™


http://www.sitening.com/

110 30th Avenue North, Suite 6

Nashville, TN 37203-6320

615-260-0005


Re: Quoting $user as Parameter to SET

From
Tom Lane
Date:
"Thomas F. O'Connell" <tfo@sitening.com> writes:
> This is an important distinction because testing reveals that the
> quoted $user after the reversal is no longer actually a dynamic
> variable that results in a search_path that resolves to the current
> user.

Really?  It works fine for me:

regression=# create schema postgres;
CREATE SCHEMA
regression=# show search_path;
 search_path
--------------
 $user,public
(1 row)

regression=# select current_schemas(true);
       current_schemas
------------------------------
 {pg_catalog,postgres,public}
(1 row)

regression=# alter database regression set search_path = public, '$user';
ALTER DATABASE
regression=# \c -
You are now connected to database "regression".
regression=# show search_path;
   search_path
-----------------
 public, "$user"
(1 row)

regression=# select current_schemas(true);
       current_schemas
------------------------------
 {pg_catalog,public,postgres}
(1 row)

regression=#

            regards, tom lane

Re: Quoting $user as Parameter to SET

From
"Thomas F. O'Connell"
Date:
Uh...

Just kidding, I guess. Wish I had a screen capture of what I had done
before because I swear I was unable to create a table in the user
namespace after having created it. But now that I look more closely
(including when running current_schemas(true)), everything looks fine.

Sorry for the noise...

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Jul 11, 2005, at 6:04 PM, Tom Lane wrote:

> "Thomas F. O'Connell" <tfo@sitening.com> writes:
>
>> This is an important distinction because testing reveals that the
>> quoted $user after the reversal is no longer actually a dynamic
>> variable that results in a search_path that resolves to the current
>> user.
>>
>
> Really?  It works fine for me:
>
> regression=# create schema postgres;
> CREATE SCHEMA
> regression=# show search_path;
>  search_path
> --------------
>  $user,public
> (1 row)
>
> regression=# select current_schemas(true);
>        current_schemas
> ------------------------------
>  {pg_catalog,postgres,public}
> (1 row)
>
> regression=# alter database regression set search_path = public,
> '$user';
> ALTER DATABASE
> regression=# \c -
> You are now connected to database "regression".
> regression=# show search_path;
>    search_path
> -----------------
>  public, "$user"
> (1 row)
>
> regression=# select current_schemas(true);
>        current_schemas
> ------------------------------
>  {pg_catalog,public,postgres}
> (1 row)
>
> regression=#
>
>             regards, tom lane
>