How to get the schema name of a table of type regclass in PostgreSQL?

15 votes

2 answers

25657 views

postgresql system-tables catalogs regclass

                          In writing a function to test if a column col_name exists in a table _tbl, I'd like to extract the table's schema name, which is passed into the function as a regclass parameter (for security??).

    CREATE OR REPLACE FUNCTION column_exists(_tbl regclass, col_name text)
      RETURNS bool AS
    $func$
      SELECT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_schema=get_schema($1) 
          AND table_name=get_table($1) 
          AND column_name=$2
        );
    $func$  LANGUAGE sql;

 So if the table name is 'staging.my_table'::regclass, I'd like to get staging from an imaginary function get_schema.

*Can I just implement this function with e.g. split_part(_tbl::text, '.', 1)?*

*In addition, is it guaranteed that the table name _tbl, when converted to text, will always have a schema name? (i.e. not omitting things such as public.)*
 
I'm not very familiar with the regclass type. I searched but couldn't find how to extract the schema name, and just wanted to ask first before re-inventing wheels.

                        

Asked by tinlyx (3820 rep)

Mar 14, 2018, 02:04 AM
Last activity: Mar 2, 2022, 02:28 PM

How to get the schema name of a table of type regclass in PostgreSQL?

Related Questions