agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedfunction source code not returning from inside a pgsql function.
4+ messages / 3 participants
[nested] [flat]
* function source code not returning from inside a pgsql function.
@ 2017-02-09 16:39 Herwig Goemans <herwig.goemans@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Herwig Goemans @ 2017-02-09 16:39 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: function source code not returning from inside a pgsql function.
@ 2017-02-09 16:44 David G. Johnston <david.g.johnston@gmail.com>
parent: Herwig Goemans <herwig.goemans@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2017-02-09 16:44 UTC (permalink / raw)
To: Herwig Goemans <herwig.goemans@gmail.com>; +Cc: pgsql-sql
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:
https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURN...
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: function source code not returning from inside a pgsql function.
@ 2017-02-09 16:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Herwig Goemans <herwig.goemans@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2017-02-09 16:57 UTC (permalink / raw)
To: Herwig Goemans <herwig.goemans@gmail.com>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: function source code not returning from inside a pgsql function.
@ 2017-02-09 17:29 Herwig Goemans <herwig.goemans@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Herwig Goemans @ 2017-02-09 17:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-02-09 17:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-02-09 16:39 function source code not returning from inside a pgsql function. Herwig Goemans <herwig.goemans@gmail.com>
2017-02-09 16:44 ` David G. Johnston <david.g.johnston@gmail.com>
2017-02-09 16:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-02-09 17:29 ` Herwig Goemans <herwig.goemans@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox