use schema name in default column value expression

Hi Mkheir

No, there is not, I'm afraid.

And it's logical, in a way: CURRENT_USER, CURRENT_DATABASE don't change for the duration of your session, while CURRENT_SCHEMA [()] will return the first valid schema found in the SEARCH_PATH , which you can change with every SET SEARCH_PATH command as often as you like in your session.

Workaround can only be that you hard-wire '1' or '2', etc into your DEFAULT clause, I'm afraid ...

A "flattened table" might help?

dbadmin=> CREATE USER jane;
CREATE USER

dbadmin=> CREATE SCHEMA DAQ3DQ;
CREATE SCHEMA

dbadmin=> ALTER USER jane SEARCH_PATH DAQ3DQ;
ALTER USER

dbadmin=> GRANT ALL ON SCHEMA DAQ3DQ TO jane;
GRANT PRIVILEGE

dbadmin=> CREATE TABLE t
dbadmin->    (
dbadmin(>       a IDENTITY(1,1) NOT NULL,
dbadmin(>       b CHAR(10) DEFAULT (SELECT SUBSTR(SPLIT_PART(search_path, ',', 1), 4, 1) FROM users WHERE user_name = user)
dbadmin(>    );
ERROR 7347:  Default queries must refer to tables

Bummer. We have to create a Vertica ROS table:

dbadmin=> \c - dbadmin
You are now connected as user "dbadmin".

dbadmin=> CREATE TABLE public.users_table AS SELECT user_name, search_path FROM users;
CREATE TABLE

dbadmin=> GRANT SELECT ON public.users_table TO jane;
GRANT PRIVILEGE

Now user JANE can create the table:

dbadmin=> \c - jane
You are now connected as user "jane".

dbadmin=> CREATE TABLE t
dbadmin->   (
dbadmin(>     a IDENTITY(1,1) NOT NULL,
dbadmin(>     b CHAR(10) DEFAULT (SELECT SUBSTR(SPLIT_PART(search_path, ',', 1), 4, 1) FROM public.users_table WHERE user_name = user)
dbadmin(>   );
CREATE TABLE

dbadmin=> INSERT INTO t (b) VALUES (DEFAULT);
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM t;
 a |     b
---+------------
 1 | 3
(1 row)