16

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.

Improve this question

2 Answers 2

Reset to default
15

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

I'm not sure that would be safe. Instead, I would use,

SELECT nspname
FROM pg_catalog.pg_class AS c
JOIN pg_catalog.pg_namespace AS ns
  ON c.relnamespace = ns.oid
WHERE c.oid = _tbl;

That is guaranteed to work.

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??).

So you could do something like,

SELECT nspname
FROM pg_catalog.pg_attribute AS a
JOIN pg_catalog.pg_class AS c
  ON a.attrelid = c.oid
JOIN pg_catalog.pg_namespace AS ns
  ON c.relnamespace = ns.oid
WHERE c.oid = _tbl
  AND a.attname = col_name;

Update

If you have a fully-qualified identifier, one with the table name, and schema name inside, You can use parse_ident to parse it safely.

Improve this answer
0
15

To further simplify, you could use a cast to regnamespace - another object identifier type introduced with Postgres 9.5

SELECT relnamespace::regnamespace::text
FROM   pg_catalog.pg_class
WHERE  oid = _tbl;

Casting an object identifier type to text produces a fully qualified (only if the current search_path requires it) and double-quoted (only if necessary) string.

But you don't need any of this if you work with pg_attribute directly:

CREATE OR REPLACE FUNCTION column_exists(_tbl regclass, _col_name text)
  RETURNS bool
  LANGUAGE sql AS
$func$
SELECT EXISTS (
   SELECT FROM pg_catalog.pg_attribute
   WHERE attrelid = _tbl
   AND   attname = _col_name
   );
$func$;

Simpler, shorter, faster.

Improve this answer
3
  • cool trick. I like it. Commented Mar 16, 2018 at 3:32
  • 1
    To get user-defined columns and not system columns, do we filter on attnum > 0? Commented Jul 3 at 9:02
  • @Rafs Yes, exactly. Commented Jul 3 at 13:25

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.