Patch to psql to allow SEARCH_PATH to be set from env
Using schemas is fun. Setting the search_path every time I use psql
isn't. This patch modifies startup.c in psql to allow the SEARCH_PATH
to be set to whatever the PG_SCHEMA_SEARCH_PATH environment variable is
set to. If the var is not defined or is empty, no action is taken and
the search path is what it would be without this patch. If
PG_SCHEMA_SEARCH_PATH is set to garbage, psql simply reports the
database error about non-existent schemas and continues running with
the search_path it would have without this patch. The patch is against
PG 7.4.1.
Here's an example session:
powerbook> PG_SCHEMA_SEARCH_PATH="'public','core','objects'"
powerbook> export PG_SCHEMA_SEARCH_PATH
powerbook> ./psql -d opencce
SET
Welcome to psql 7.4.1, 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
opencce=# show search_path;
search_path
-----------------------
public, core, objects
(1 row)
opencce=#
The patch is small enough to copy and paste here; I've also attached it
to this message.
thanks,
/s.
PATCH:
diff -c -r postgresql-7.4.1/src/bin/psql/startup.c
postgresql-7.4.1.mod/src/bin/psql/startup.c
*** postgresql-7.4.1/src/bin/psql/startup.c Mon Sep 29 13:21:33 2003
--- postgresql-7.4.1.mod/src/bin/psql/startup.c Mon Feb 9 17:52:08 2004
***************
*** 98,103 ****
--- 98,105 ---- char *username = NULL;
char *password = NULL;
+ char *potential_SearchPath = NULL;
+ char *searchpath = NULL;
bool need_pass; setlocale(LC_ALL, "");
***************
*** 209,214 ****
--- 211,227 ----
PQsetNoticeProcessor(pset.db, NoticeProcessor, NULL); SyncVariables();
+
+ /*
+ * Set schema search path from environment
+ */
+
+ potential_SearchPath = getenv("PG_SCHEMA_SEARCH_PATH");
+ if (potential_SearchPath != NULL) {
+ searchpath = (char *) palloc(sizeof("SET SEARCH_PATH TO ") +
strlen(potential_SearchPath));
+ sprintf(searchpath, "SET SEARCH_PATH TO %s",
potential_SearchPath);
+ successResult = SendQuery(searchpath) ? EXIT_SUCCESS :
EXIT_FAILURE;
+ } if (options.action == ACT_LIST_DB)
{
Attachments:
searchpath.patchapplication/octet-stream; name=searchpath.patch; x-unix-mode=0444Download+13-0
Scott Goodwin wrote:
Using schemas is fun. Setting the search_path every time I use psql
isn't. This patch modifies startup.c in psql to allow the SEARCH_PATH to
be set to whatever the PG_SCHEMA_SEARCH_PATH environment variable is set
to. If the var is not defined or is empty, no action is taken and the
search path is what it would be without this patch. If
PG_SCHEMA_SEARCH_PATH is set to garbage, psql simply reports the
database error about non-existent schemas and continues running with the
search_path it would have without this patch. The patch is against PG
7.4.1.
Do you know that you can go ALTER USER yourselft SET SEARCH_PATH TO
"'public','core','objects'"; So that will always be your default path?
Chris
Hi Chris,
No, I didn't know that, but I figured there might be another, better
way to do this that I hadn't found. Thanks for the info; I'm going to
dig into ALTER USER to see what else I may be missing. Consider this
patch "rescinded", though it was a good exercise in working with PG
source code. If anyone cares to, I'd still like feedback on anything I
could have done better in this patch (i.e. coding style, better place
to put this kind of code etc.).
thanks,
/s.
On Feb 10, 2004, at 8:37 AM, Christopher Kings-Lynne wrote:
Show quoted text
Scott Goodwin wrote:
Using schemas is fun. Setting the search_path every time I use psql
isn't. This patch modifies startup.c in psql to allow the SEARCH_PATH
to be set to whatever the PG_SCHEMA_SEARCH_PATH environment variable
is set to. If the var is not defined or is empty, no action is taken
and the search path is what it would be without this patch. If
PG_SCHEMA_SEARCH_PATH is set to garbage, psql simply reports the
database error about non-existent schemas and continues running with
the search_path it would have without this patch. The patch is
against PG 7.4.1.Do you know that you can go ALTER USER yourselft SET SEARCH_PATH TO
"'public','core','objects'"; So that will always be your default path?Chris
No, I didn't know that, but I figured there might be another, better way
to do this that I hadn't found. Thanks for the info; I'm going to dig
into ALTER USER to see what else I may be missing. Consider this patch
"rescinded", though it was a good exercise in working with PG source
code. If anyone cares to, I'd still like feedback on anything I could
have done better in this patch (i.e. coding style, better place to put
this kind of code etc.).
I have the feeling it wouldn't have been accepted because it was kind of
'arbitrary'. It's generally a good idea to ask the -hackers list first
if a patch is worth writing before actually writing it ;)
Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
Do you know that you can go ALTER USER yourselft SET SEARCH_PATH TO
"'public','core','objects'"; So that will always be your default path?
Another possibility is to issue the SET from a ~/.psqlrc file (I think
that's what it's called, check the man page).
It makes sense to support environment variables for connection
parameters, since those can't be gotten from the database (for obvious
reasons) nor from ~/.psqlrc (which isn't read till after connecting).
But I'm not eager to support environment variables for things that can
be set those ways. There are a heck of a lot of SET variables --- would
we want an env var for each one? (Seen in this light, PGCLIENTENCODING
is a wart, but I suppose we have to keep it for backwards compatibility.)
regards, tom lane
On Feb 10, 2004, at 9:51 AM, Christopher Kings-Lynne wrote:
I have the feeling it wouldn't have been accepted because it was kind
of 'arbitrary'. It's generally a good idea to ask the -hackers list
first if a patch is worth writing before actually writing it ;)
Ah well, it was worth the experience.
On Feb 10, 2004, at 10:30 AM, Tom Lane wrote:
But I'm not eager to support environment variables for things that can
be set those ways. There are a heck of a lot of SET variables ---
would
we want an env var for each one?
I agree; simpler is better and there's already a simple way to do it.
The environment's polluted enough as it is.
thanks,
/s.
Scott Goodwin <scott@scottg.net> writes:
If anyone cares to, I'd still like feedback on anything I could have
done better in this patch
Since, you asked, two minor points: (1) palloc() is for memory
allocation in the backend; use malloc() (or a wrapper like
pg_malloc()) in frontend apps and libpq (2) naming variables like
word1_Word2 is inconsistent with the surrounding code.
-Neil
Tom Lane <tgl@sss.pgh.pa.us> writes:
Another possibility is to issue the SET from a ~/.psqlrc file (I think
that's what it's called, check the man page).
Yet another possibility is to use the existing PGOPTIONS env var:
%PGOPTIONS='-c search_path=foo,public' psql template1
[ ... ]
template1=# show search_path;
search_path
-------------
foo,public
(1 row)
(Seen in this light, PGCLIENTENCODING is a wart, but I suppose we
have to keep it for backwards compatibility.)
I seems to me that PGGEQO, PGTZ, and PGDATESTYLE fall into the same
category.
-Neil
Hi Neil,
I also saw an mm_alloc or something similar in another area of code.
I'll spend some time reading through the sources properly before I
attempt another mod. Thanks for the feedback -- much appreciated.
/s.
On Feb 10, 2004, at 3:24 PM, Neil Conway wrote:
Show quoted text
Scott Goodwin <scott@scottg.net> writes:
If anyone cares to, I'd still like feedback on anything I could have
done better in this patchSince, you asked, two minor points: (1) palloc() is for memory
allocation in the backend; use malloc() (or a wrapper like
pg_malloc()) in frontend apps and libpq (2) naming variables like
word1_Word2 is inconsistent with the surrounding code.-Neil