Re: user name doesn't seem to resolve to $user in search_path

Tom,
Thanks for the tip.

I discovered that the problem was solved by telling Postgres

CREATE SCHEMA tstuser AUTHORIZATION tstusr

(maybe it was not clear that this was done by user postgres and that the
table creation was done by user tstuser)

So, the $user is being resolved correctly, provided the user has the
proper rights.
However,
I still have one problem.
If I have set the search_path as follows

SET search_path TO myschema

the $user is removed from the search_path.

How do I get it back?

SET search_path TO $user

gives an parsing error caused by the $ sign.

Vriendelijke groeten, Kind regards,
Frank Messie

pgsql-admin-owner(at)postgresql(dot)org wrote on 16-10-2003 19:29:53:

> frank(dot)messie(at)osix(dot)nl writes:
> > I have a problem with the following.
> > I start psql as user "testusr" in database "testdb"
> > In the database I have created a schema with the name of a user
> > CREATE SCHEMA testusr ;
> > The search_path is the default $user,public
> > Then I create a table
> > CREATE TABLE test (name char(8)) ;
> > Now I get the error message "No namespace have been selected to create

> > in".
>
> This sounds like the search path didn't get recomputed when you created
> the new schema, but I cannot reproduce any such problem:
>
> regression=# create user tstuser;
> CREATE USER
> regression=# grant create on database regression to tstuser;
> GRANT
> regression=# \c - tstuser
> You are now connected as new user "tstuser".
> regression=> select current_schemas(true);
> current_schemas
> ---------------------
> {pg_catalog,public}
> (1 row)
>
> regression=> create schema tstuser;
> CREATE SCHEMA
> regression=> CREATE TABLE test (name char(8)) ;
> CREATE TABLE
> regression=> \dt test
> List of relations
> Schema | Name | Type | Owner
> ---------+------+-------+---------
> tstuser | test | table | tstuser
> (1 row)
>
> regression=> select current_schemas(true);
> current_schemas
> -----------------------------
> {pg_catalog,tstuser,public}
> (1 row)
>
> Are you sure you spelled the schema name the same as the user name?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings