Thread: [SQL] function source code not returning from inside a pgsql function.

[SQL] function source code not returning from inside a pgsql function.

From
Herwig Goemans
Date:
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.

From
"David G. Johnston"
Date:
On Thu, Feb 9, 2017 at 9:39 AM, Herwig Goemans <herwig.goemans@gmail.com> wrote:
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_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 ?

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:


Re: [SQL] function source code not returning from inside a pgsql function.

From
Tom Lane
Date:
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



Re: [SQL] function source code not returning from inside a pgsql function.

From
Herwig Goemans
Date:
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