我得到这个错误:
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 2I 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 $$ DECLAREAnd 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$ DECLAREEDIT: 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 iwbAnd it worked!
更多推荐
发布评论