2

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.

1 Answer 1

3

pg_get_functiondef does exactly what you want:

SELECT pg_get_functiondef('public.my_func'::regproc);
Sign up to request clarification or add additional context in comments.

4 Comments

Sometimes a humble "l" makes the difference!! :-)
@coterobarros That may well be, but I have no idea how that applies to the question or the answer.
:)) I copied the select sentence, then I run into "pubic" problems! But nothing sexual
I see. Thanks for the clarification. I read the "l" as an "I" and was left clueless.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.