function source code not returning from inside a pgsql function.

From: Herwig Goemans <herwig(dot)goemans(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: function source code not returning from inside a pgsql function.
Date: 2017-02-09 16:39:19
Message-ID: CAKaDz_FUMP-ELy3ckGrqc=vGmsW-4oYqMs2UeWzRbkzRHU8JjA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message David G. Johnston 2017-02-09 16:44:46 Re: function source code not returning from inside a pgsql function.
Previous Message Tom Lane 2017-02-08 15:52:38 Re: Problem wil TIMEZONE vs TIME ZONE