Обсуждение: [SQL] function source code not returning from inside a pgsql function.
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
Re: [SQL] function source code not returning from inside a pgsql function.
От
"David G. Johnston"
Дата:
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
[...]
And I call the function with :TM=# select alter_tbl('xtm_2_fr_nl_doc');NOTICE: input parameters result in xtm_2_fr_nl_docNOTICE: probleemalter_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 ?
You've defined the function as RETURNING void -- which says you don't intend to return anything to the caller.
You need to redefine the function and then explicitly return whatever content it is you wish to return - per the documentation at:
Herwig Goemans <herwig.goemans@gmail.com> writes:
> v_doc_fnc = replace(v_doc_fnc,'public.',null);
I'm pretty sure that will result in NULL always. NULL is not another
spelling for the empty string, no matter what Oracle says ;-)
regards, tom lane
Tom,
You are right, that was the problem.
A reminder for me to read the function definitions I am using.
regards,
Herwig
2017-02-09 17:57 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
Herwig Goemans <herwig.goemans@gmail.com> writes:
> v_doc_fnc = replace(v_doc_fnc,'public.',null);
I'm pretty sure that will result in NULL always. NULL is not another
spelling for the empty string, no matter what Oracle says ;-)
regards, tom lane