未正确转储或恢复postgres函数/扩展？

Question

更新

自我回答，请看这里的第一个评论和我自己的答案。

原始问题

我正在使用一些sql文件设置一个本地开发postgres，其中一个文件创建了一个用于距离计算的自定义函数。扩展“多维数据集”和“地球距离”，该函数按预期工作。

我正在转储本地开发postgres db，sql转储包含扩展创建和自定义函数-看起来很好。

我在另一台测试机器上导入转储，没有错误消息。

但是在这里，当我使用这个函数时，我得到了一个异常：

Caused by: org.postgresql.util.PSQLException: ERROR: function ll_to_earth(numeric, numeric) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Where: PL/pgSQL function opengeodb_radius_selection(numeric,numeric,integer,integer) line 3 at RETURN QUERY

设置本地数据库时使用的完整SQL代码：

CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;

CREATE FUNCTION
    opengeodb_radius_selection(
        baseLatitude numeric(9,6),
        baseLongitude numeric(9,6),
        radius_in_metres integer,
        opengeodb_level integer)
    RETURNS TABLE(
        locid integer,
        name character varying(255),
        distance float8)
AS
$func$
BEGIN
    RETURN QUERY
    SELECT subQueryAlias.locid, subQueryAlias.name, subQueryAlias.distance
FROM
(
    SELECT *, earth_distance(ll_to_earth(baseLatitude,baseLongitude), ll_to_earth(lat,lon)) as distance
    FROM opengeodb
    GROUP BY opengeodb.locid, opengeodb.lat, opengeodb.lon, opengeodb.name, distance
) subQueryAlias
WHERE plz is not null
AND plz <> ''
AND level = opengeodb_level
AND earth_box(ll_to_earth(lat,lon),radius_in_metres) @> ll_to_earth(baseLatitude,baseLongitude)
AND subQueryAlias.distance <= radius_in_metres
ORDER BY subQueryAlias.distance;
END
$func$ LANGUAGE plpgsql;

转储中的完整SQL代码：

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


--
-- Name: cube; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS cube WITH SCHEMA public;


--
-- Name: EXTENSION cube; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION cube IS 'data type for multidimensional cubes';


--
-- Name: earthdistance; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS earthdistance WITH SCHEMA public;


--
-- Name: EXTENSION earthdistance; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION earthdistance IS 'calculate great-circle distances on the surface of the Earth';


SET search_path = public, pg_catalog;

--
-- Name: opengeodb_radius_selection(numeric, numeric, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION opengeodb_radius_selection(baselatitude numeric, baselongitude numeric, radius_in_metres integer, opengeodb_level integer) RETURNS TABLE(locid integer, name character varying, distance double precision)
    LANGUAGE plpgsql
    AS $$
BEGIN
    RETURN QUERY
    SELECT subQueryAlias.locid, subQueryAlias.name, subQueryAlias.distance
    FROM
    (
        SELECT *, earth_distance(ll_to_earth(baseLatitude,baseLongitude), ll_to_earth(lat,lon)) as distance
        FROM opengeodb
        GROUP BY opengeodb.locid, opengeodb.lat, opengeodb.lon, opengeodb.name, distance
    ) subQueryAlias
    WHERE plz is not null
    AND plz <> ''
    AND level = opengeodb_level
    AND earth_box(ll_to_earth(lat,lon),radius_in_metres) @> ll_to_earth(baseLatitude,baseLongitude)
    AND subQueryAlias.distance <= radius_in_metres
    ORDER BY subQueryAlias.distance;
END
$$;


ALTER FUNCTION public.opengeodb_radius_selection(baselatitude numeric, baselongitude numeric, radius_in_metres integer, opengeodb_level integer) OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = false;

...(followed by table creation and so on)

这是我的错还是错误的行为？

Answer 1

自我回答(见第一个问题评论)

在debian系统上安装了9.4.10，对于debian没有postgresql-cont肋骨9.4.10 (但是在我的开发窗口postgres包中，也有9.4.10)

我们已经将debian系统更新到9.4.12，现在包含了cont肋骨，这个功能和预期的一样好。

Answer 2

ll_to_earth被定义为接受参数(float8, float8)，但您传递的是numeric, numeric。因此，您需要向您的函数添加一些强制转换。

现在还不清楚为什么它以前起作用了。也许你是从旧版本升级的？