Thread: [BUGS] Regarding copy of function from one db to another db
Hello sir,
if we need to copy all the functions of database to another database then how we can implement it other then copy paste and run it...Because there are total 153 functions in our database.
Please Reply Us.
Thank u sir,
HI, i think that is not a bug, try to execute that query, this return
the code of functions of specific schema (schema_name)
select 'CREATE OR REPLACE FUNCTION '||
nsp.nspname||'.'||proname||'('||pg_get_function_identity_arguments(p.oid)||')
RETURNS ' ||
case
when proretset='t' THEN ' SETOF '
else ''
end || typname || ' AS $BODY$ ' ||prosrc || ' $BODY$ LANGUAGE ' ||
l.lanname ||';' from pg_proc p join pg_namespace nsp ON
p.pronamespace = nsp.oid join pg_type t ON p.prorettype = t.oid join pg_language l ON p.prolang = l.oid where
nsp.nspname = 'schema_name'
Greetings
PD: maybe use copy to export result and load in the other place
On 15/06/17 06:43, Rohit Dwivedi wrote:
> Hello sir,
> if we need to copy all the functions of database to
> another database then how we can implement it other then copy paste
> and run it...Because there are total 153 functions in our database.
>
> Please Reply Us.
>
> Thank u sir,
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Hello sir,if we need to copy all the functions of database to another database then how we can implement it other then copy paste and run it...Because there are total 153 functions in our database.
General questions should be directed to the pgsql-general@postgresql.org mailing list.
The tools provided to do what you want are pg_dump and pg_restore (specifically note the --list and --list-file options to pg_restore). You will need to manually edit the list file to include only the functions as there is no object type filter capability (well, I now see there is a --function argument to pg_restore but I'm unsure about whether it take wildcards. You will probably want to try it). You can restrict the dump to schema only if you indeed only care about functions.
David J.