I am searching for a way to display a command that creates a certain routine in PostgreSQL. I found a solution:
SELECT routine_definition
FROM information_schema.routines
WHERE routine_type='FUNCTION' AND specific_schema='public' AND routine_name = 'my_func'
That returns in my case:
begin
insert into person ("name", "age") values (_name, _age)
on conflict on constraint person_name_uk do update
set age = _age
where person.name = _name;
end
But I need the output like this:
CREATE OR REPLACE FUNCTION public.my_func(_name character varying, _age integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
begin
insert into person ("name", "age") values (_name, _age)
on conflict on constraint person_name_uk do update
set age = _age
where person.name = _name;
end
$function$
;
I have looked through the fields of information_schema.routines and found nothing that could help me construct the output I need. Probably, there's another place where it's stored or a standard function that I can use.
I need a solution without the slash at the beginning. I mean, anything like \ef doesn't fit because I can't use it inside of complex statements.