Recreating function definitions in PostgreSQL

Question

I'm trying to recreate function definitions between a database on Postgres 8.4 and one running on 9.3.

I dumped the function definitions on the 8.4 server using

    SELECT pg_get_functiondef(f.oid)
    FROM pg_catalog.pg_proc f
    INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid)
    WHERE n.nspname = 'public';

Which gives me output like so:

CREATE OR REPLACE FUNCTION public.delete_fentity()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN
        DELETE FROM fentities WHERE fitnummer=OLD.fitnummer;
        RETURN new;
END
$function$

CREATE OR REPLACE FUNCTION public.validate_leveranciersrollen_deletion()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN
        IF      NOT EXISTS 
                        (
                        SELECT  *
                        FROM    leveranciersrollen
                        WHERE   organisaties_id=OLD.organisaties_id
                        AND     levrol='contactpersoon'
                        )
                AND
                        (
                        EXISTS 
[....]

However when trying to load this sql script into 9.3 it gives me a syntax error.

ERROR:  syntax error at or near "CREATE"
LINE 11: CREATE OR REPLACE FUNCTION public.validate_leveranciersrolle

What am I doing wrong here?

Should there not be a semi-colon after the END line of each function? — Glenn
– Glenn, Commented Apr 12, 2016 at 12:07
@Glenn after END it's optional, but there should be one between the create statements (after each closing $function$ ). — pozs
– pozs, Commented Apr 12, 2016 at 15:28

ADEpt · Accepted Answer · 2016-04-12 13:46:08Z

1

Consider doing pg_dump -U $user -h $host -s -n <schema name> and take function definitions from there. As a bonus, you will get all permission-setting statements as well.

answered Apr 12, 2016 at 13:46

ADEpt

5,5521 gold badge28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Recreating function definitions in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related