Thread: Setting schema from command line in psql

I keep a large number of separate development projects in a single
database, each in its own schema. Whenever I wish to work on one of them
in psql, after running

  psql <dbname>

I then have to type

  SET search_path TO <schemaname> [, PUBLIC] ;

I would really like to be able to do this from the command line, so that
I can make a wrapper for it, something like 'openproject <pname>'.

I read through the manual and the closest thing I could find was --set,
but this handles psql environment variables, which do not, as far as I
can see, affect the search_path.

Ideally I would like something like this:

  psql --schema <schemaname>    <- pg_dump already supports this

Or, perhaps better:

  psql --before-starting-execute-this-command "SET search_path....

(or a similar more digestable syntax)

Have I missed something in the docs ?

Thanks,

Daniel

I've never tried that, but have you experimented with setting the schema
from the .psqlrc file? If you can do that then you might be able to get
.psqlrc to set the schema  to a variable which you have set using the -v
command-line option.

It's just a thought ... I've never tried.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of
psql-novice@netzach.co.il
Sent: 12 April 2007 14:57
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Setting schema from command line in psql



I keep a large number of separate development projects in a single
database, each in its own schema. Whenever I wish to work on one of them
in psql, after running

  psql <dbname>

I then have to type

  SET search_path TO <schemaname> [, PUBLIC] ;

I would really like to be able to do this from the command line, so that
I can make a wrapper for it, something like 'openproject <pname>'.

I read through the manual and the closest thing I could find was --set,
but this handles psql environment variables, which do not, as far as I
can see, affect the search_path.

Ideally I would like something like this:

  psql --schema <schemaname>    <- pg_dump already supports this

Or, perhaps better:

  psql --before-starting-execute-this-command "SET search_path....

(or a similar more digestable syntax)

Have I missed something in the docs ?

Thanks,

Daniel


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

> I've never tried that, but have you experimented with setting the schema
> from the .psqlrc file? If you can do that then you might be able to get
> .psqlrc to set the schema  to a variable which you have set using the -v
> command-line option.

Ingenious. Works a dream. Here are the details of how I did it just in
case somebody else is interested:

begin ~/.psqlrc --------

SET search_path TO :schema;

end file ------------------


mycomputer:~$ psql -v schema=myschema mydb
Welcome to psql 7.old.reallyold, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

SET
mydb=> \dt
                      List of relations
    Schema    |             Name             | Type  | Owner
--------------+------------------------------+-------+--------
   myschema   |         table1               | table |  me
   myschema   |         table2               | table |  me
   myschema   |         table3               | table |  me