Postgresql错误类型“”不存在(Postgresql Error type “ ” does not exist)

Postgresql错误类型“”不存在(Postgresql Error type “ ” does not exist)

我得到这个错误：

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

我正在像这样在Docker容器中恢复我的数据库：

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/

这里是schema.sql文件中的类型定义：

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

以下是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

这就是数据库恢复的方式：

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

编辑：我改变了dockerfile在函数之前创建类型：

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/

我如何解决它？

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?

最满意答案

你确定类型是在函数之前创建的吗？

与该函数在同一模式中创建的类型是什么？ （不是在使用的sql文件中的某处设置了search_path？）

$$和$ BODY $启动“dolar引用的字符串”，它们由同一个（$$或$ BODY $）终止。 为什么postgres使用$ BODY $而不是$ ILOVEBEER $仍然是未知的，为什么postgres使用$ BODY $，而不是BODY，你可以有任何字符（或任何），它只允许在字符串中写入$$等。

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!