CODEWITHSUNDEEP
sqldatabasepostgresqlpostgresql-9.3psql
Vasil
Vasil

Reputation: 38106

Recreating function definitions in PostgreSQL

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?

Upvotes: 0

Views: 277

Answers (1)

ADEpt
ADEpt

Reputation: 5542

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.

Upvotes: 1

Related Questions