Postgresql Error type " " does not exist

Question

I get this error:

    Caused by: org.postgresql.util.PSQLException: ERROR: type 
    "tool_parse_numbers_record" does not exist
    Where: compilation of PL/pgSQL function "tool_parse_numbers" near line 2

I am restoring my database in a docker container like this:

    FROM postgres:9.4
    ENV POSTGRES_USER iwb
    ENV POSTGRES_PASSWORD 1907
    ENV POSTGRES_DB iwb

    ADD ./1_schema.sql /docker-entrypoint-initdb.d/
    ADD ./2_data.sql /docker-entrypoint-initdb.d/

Here's type definition in schema.sql file:

    CREATE TYPE tool_parse_numbers_record AS (
     satir character varying(4000)
    );

Here's top part of function definition in schema.sql:

    CREATE FUNCTION tool_parse_numbers(pstr text, pdelimeter text DEFAULT 
    '|'::text) RETURNS SETOF tool_parse_numbers_record
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
    DECLARE

And this is how database is restored:

    CREATE FUNCTION tool_parse_numbers(pstr text, pdelimeter text DEFAULT 
    '|'::text) RETURNS SETOF tool_parse_numbers_record
    LANGUAGE plpgsql SECURITY DEFINER
    AS $BODY$
    DECLARE

EDIT: I changed dockerfile to create types before functions:

    ADD ./1_type.sql /docker-entrypoint-initdb.d/
    ADD ./2_table.sql /docker-entrypoint-initdb.d/
    ADD ./3_func.sql /docker-entrypoint-initdb.d/
    ADD ./4_rest_table.sql /docker-entrypoint-initdb.d/
    ADD ./5_data.sql /docker-entrypoint-initdb.d/

How do I fix it?

Solution 1

When I extracted the database to schema.sql file, owner was set to postgres:

    ALTER FUNCTION iwb.tool_parse_numbers(pstr text, pdelimeter text) OWNER TO postgres; 

So I changed owner to iwb since it's like that in docker file

    ENV POSTGRES_USER iwb

And it worked!

Solution 2

Are you sure that the type is created before the function?

Is the type created in the same schema as the function? (Is not SET search_path somewhere in the sql file used?)

$$ and $BODY$ starts "dolar quoted string", which are terminated by the same ($$ or $BODY$). Instead of BODY you can have any characters (or any), it just allowes to write $$, ", etc. inside the string without problems. Why postgres use $BODY$ and not $ILOVEBEER$ remains unknown.

Comments

[a_horse_with_no_name]

There is no difference between $$ and $BODY$ - that's not the reason for your error. Apparently when you create the function the type has not been created yet. Unrelated, but: why are you using a type to begin with. You could simply define your function as returns table (satir varchar(4000))

[Nick Barnes]

@a_horse_with_no_name: Type modifiers in function signatures are discarded (take a look at pg_proc; no typmod fields in sight). If you want them preserved, you need a custom type (though a domain would usually be simpler than a one-column composite).

[Uluz]

Yes, the type is created in same schema as the function. I changed docker file to create types before functions as shown in edit of the question. And I am still getting the error.