Hello,
I have a function in a punlic schema and in psql when I do:
pg_get_functiondef(oid) from pg_proc where proname = 'xtm_doc_ref';
I get a result.
When I do the same in a function:
CREATE OR REPLACE FUNCTION tm.alter_tbl(
p_id integer,
p_type character varying,
p_lang character varying)
RETURNS void
LANGUAGE 'plpgsql'
COST 100.0
VOLATILE NOT LEAKPROOF SECURITY DEFINER
AS $function$
declare
v_sql_text varchar(4000);
v_tab constant varchar(30) default lower(concat_ws('_','xtm' ,p_id , p_lang, p_type ));
v_uq_idx constant varchar(30) default lower(concat_ws('_','uq_xtm' ,p_id , p_lang, p_type ));
v_suffix varchar(4) default case substring((string_to_array(version(),' '))[2] from 1 for 1) when '9' then '_tab' else null end ; -- if is is a version 9 instance.
v_doc_fnc varchar(4000);
v_dic_fnc varchar(4000);
begin
RAISE notice 'input parameters result in %', v_tab;
v_doc_fnc := pg_get_functiondef(oid) from pg_proc where proname = 'xtm_doc_ref';
v_doc_fnc = replace(v_doc_fnc,'public.',null);
v_doc_fnc = replace(v_doc_fnc,'xtm_doc_ref',v_tab);
raise notice ' %', coalesce(v_doc_fnc,'probleem');
END;
$function$;
And I call the function with :
TM=# select alter_tbl('xtm_2_fr_nl_doc');
NOTICE: input parameters result in xtm_2_fr_nl_doc
NOTICE: probleem
alter_tbl
-----------
(1 row)
The source code is not fetched.
the function the public schema is a template for another function that I want to create in the TM schema.
I suppose this has something to do with pgsql nit having acces to public schema ?
Is there something that can be done about it ?
Kind regards,
Herwig