get schema of table in postgresql

user_19922118 0
Viewed 43

Background my Procedure looks like this

CREATE OR REPLACE PROCEDURE c_sch.COMPUTETABLECOMMENT (p_BASETABLENAME in VARCHAR, p_sCOMMENT in out VARCHAR)
AS $$ 
DECLARE
  v_sComment VARCHAR;
BEGIN
  p_sCOMMENT:='Logtable for '||p_BASETABLENAME;
  select obj_description('myschema'.p_BASETABLENAME ::regclass, 'pg_class') into into v_sComment;
  
  IF v_sComment is not NULL THEN
    p_sCOMMENT:=substr(c_sch.ExpandComment(p_sCOMMENT||': '||v_sComment),1,4000);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    p_sCOMMENT:='Logtable for '||p_BASETABLENAME;
END;
$$ LANGUAGE plpgsql

Problem I want this procedure to work without me changing the 'myschema' manually for every table i run this procedure with.

question is there a way to get the schema of a given table?

plpgsql postgresql
1 Answers

I think the easiest is just to remove 'myschema' from the procedure and then set the search path:

SET search_path TO myschema;

after that postgres will search objects only in myschema and you won't need to change the schema.

user_71936480
Related