Subject: search_path wildcard?

From: Ron Johnson <[email-masked]>
Date: Wed, 22 May 2024 12:35:51 -0400

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_%';

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 22 May 2024 10:53:31 -0600

> ALTER ROLE foo SET SEARCH_PATH  = '*';

Correct, you cannot do that.

David J.

---

From: Ron Johnson <[email-masked]>
Date: Wed, 22 May 2024 13:54:21 -0400

> Correct, you cannot do that.

That would be a helpful feature for administrators, when there are multiple
schemas in multiple databases, on multiple servers: superusers get ALTER
ROLE foo SET SEARCH_PATH  = '*'; and they're done with it.

---

From: Pavel Stehule <[email-masked]>
Date: Wed, 22 May 2024 21:34:39 +0200

st 22. 5. 2024 v 19:54 odesílatel Ron Johnson <[email-masked]>
napsal:

> That would be a helpful feature for administrators, when there are
> multiple schemas in multiple databases, on multiple servers: superusers get ALTER

It can be pretty dangerous, because you don't specify order of schemas

Regards

Pavel