DO $$
declare
_rec record;
_args text;
s text;
begin
for _rec in (
--113
SELECT
n.nspname AS schema
,proname AS fname
--,proargnames AS args
--,t.typname AS return_type
--,d.description
--,pg_get_functiondef(p.oid) as definition
,sum(1) over(partition by n.nspname||'.'||proname) as rn
FROM pg_proc p
JOIN pg_type t
ON p.prorettype = t.oid
LEFT OUTER
JOIN pg_description d
ON p.oid = d.objoid
LEFT OUTER
JOIN pg_namespace n
ON n.oid = p.pronamespace
WHERE n.nspname not in ('pg_catalog','pkg_process','pkg_user_management') and
provolatile='s'
and proname not in ('getphotoids','fnc_get_dict_element','f_increment_num')
) loop
SELECT pg_get_function_identity_arguments((_rec.schema||'.'||_rec.fname)::regproc) into _args;
s:='alter function '||_rec.schema||'.'||_rec.fname||'('||_args||') VOLATILE;';
raise notice '%',s;
execute s;
end loop;
END
$$
alter function pkg_get_cabinet.get_zas_mrg_guest_list(INOUT refcur refcursor, in_comment_id integer) VOLATILE;
declare
_rec record;
_args text;
s text;
begin
for _rec in (
--113
SELECT
n.nspname AS schema
,proname AS fname
--,proargnames AS args
--,t.typname AS return_type
--,d.description
--,pg_get_functiondef(p.oid) as definition
,sum(1) over(partition by n.nspname||'.'||proname) as rn
FROM pg_proc p
JOIN pg_type t
ON p.prorettype = t.oid
LEFT OUTER
JOIN pg_description d
ON p.oid = d.objoid
LEFT OUTER
JOIN pg_namespace n
ON n.oid = p.pronamespace
WHERE n.nspname not in ('pg_catalog','pkg_process','pkg_user_management') and
provolatile='s'
and proname not in ('getphotoids','fnc_get_dict_element','f_increment_num')
) loop
SELECT pg_get_function_identity_arguments((_rec.schema||'.'||_rec.fname)::regproc) into _args;
s:='alter function '||_rec.schema||'.'||_rec.fname||'('||_args||') VOLATILE;';
raise notice '%',s;
execute s;
end loop;
END
$$
alter function pkg_get_cabinet.get_zas_mrg_guest_list(INOUT refcur refcursor, in_comment_id integer) VOLATILE;