MIME-Version: 1.0
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 22 May 2024 12:35:51 -0400
Message-ID: <CANzqJaDxVFRPreSz_e=cg2PU2nW48BUcoJTyvc72Yry0tqkTcQ@mail.gmail.com>
Subject: search_path wildcard?
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000b9040f06190d87c2"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaDxVFRPreSz_e%3Dcg2PU2nW48BUcoJTyvc72Yry0tqkTcQ%40mail.gmail.com>
Precedence: bulk

--000000000000b9040f06190d87c2
Content-Type: text/plain; charset="UTF-8"

This doesn't work, and I've found nothing similar:
ALTER ROLE foo SET SEARCH_PATH  = '*';

Is there a single SQL statement which will generate a search path based
on information_schema.schemata, or do I have to write an anonymous DO
procedure?
SELECT schema_name FROM information_schema.schemata WHERE schema_name !=
'information_schema' AND schema_name NOT LIKE 'pg_%';

--000000000000b9040f06190d87c2
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>This doesn&#39;t work, and I&#39;ve found nothing sim=
ilar:</div><font face=3D"monospace">ALTER ROLE foo SET SEARCH_PATH=C2=A0 =
=3D &#39;*&#39;;</font><div><br></div><div>Is there a single SQL statement =
which will generate a search path based on=C2=A0information_schema.schemata=
, or do I have to write an anonymous DO procedure?</div><div><font face=3D"=
monospace">SELECT schema_name FROM information_schema.schemata WHERE schema=
_name !=3D &#39;information_schema&#39; AND schema_name NOT LIKE &#39;pg_%&=
#39;;</font><br></div><div><br></div></div>

--000000000000b9040f06190d87c2--