3

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.

5

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.

2

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 pg_temp.column_exists(_tbl regclass, _col_name text)
   RETURNS bool AS
$func$
SELECT EXISTS (
   SELECT FROM pg_catalog.pg_attribute
   WHERE attrelid = _tbl
   AND   attname = _col_name
   );
$func$  LANGUAGE sql;

Simpler, shorter, faster.

  • cool trick. I like it. – Evan Carroll Mar 16 '18 at 3:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.