On 7/5/22 20:55, Bryn Llewellyn wrote:
//
/david.g.johnston@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx> wrote:
/
tgl@xxxxxxxxxxxxx <mailto:tgl@xxxxxxxxxxxxx> wrote:
search_path's value is not a SQL name. It's a list of SQL
names wrapped in a string ... and the list can be empty.
This doesn't seem to be correct - wrapping them in single quotes in
the SET command ends up behaving as if you wrapped them in double
quotes anywhere else (and wrapping them individually in double quotes
here works just fine too).
And then...
/adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx> wrote:/
Those are creating objects. Set search_path is setting a configuration
value. Pretty sure it is:
{ TO | = } { value | 'value' | DEFAULT
There's different use cases. For example:
*set my_namspace.x = 'Dog house';*
*show my_namspace.x ;
Not sure what your point is?
*
I can't reconcile what you three (Tom, David, and Adrian) have said. I'm
interested to hear how you interpret what I showed in this reply:
https://www.postgresql.org/message-id/48E1391E-5A21-4736-B4B1-8B9468ECAFD4%40yugabyte.com
<https://www.postgresql.org/message-id/48E1391E-5A21-4736-B4B1-8B9468ECAFD4%40yugabyte.com>
and in particular to this:
*create schema "s1, s2";
create table "s1, s2".t(k int);
insert into "s1, s2".t(k) values(42);
set search_path = "s1, s2";*
*show **search_path;**
select k from t;*
OR (with single quotes in "set search_path":
*create schema "s1, s2";
create table "s1, s2".t(k int);
insert into "s1, s2".t(k) values(42);
set search_path = 's1, s2';
**show **search_path;**
**select k from t;
*
From here
https://www.postgresql.org/docs/current/runtime-config-client.html:
search_path (string)
...
The value for search_path must be a comma-separated list of schema names
...
By quoting the above in either single or double quotes you change what
looks like a list pf schemas into a single schema:
show search_path;
search_path
-------------
"s1, s2"
If you want that to be a list of schemas then:
set search_path = s1, s2;
SET
show search_path;
search_path
-------------
s1, s2
I get a resounding 42 in both cases. Now try this:
*set search_path = no_such_schema, "No Such Schema";*
*show **search_path;**
Which is same as:
set search_path = no_such_schema, 'No Such Schema';
show search_path;
search_path
----------------------------------
no_such_schema, "No Such Schema"
Since the list of names will end up being identifiers for schema any
name with spaces needs to be quoted. Otherwise:
set search_path = no_such_schema, No Such Schema;
ERROR: syntax error at or near "Such"
LINE 1: set search_path = no_such_schema, No Such Schema;
*
All outcomes accord with the mental model that you tell me is wrong.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
