代码之家 › 专栏 › 技术社区 › Paul

具有松散返回数据类型的PostgreSQL函数

entity-attribute-value plpgsql function postgresql

-1

Paul · 技术社区 · 6 年前

CREATE TABLE my_report
(
  id               serial                      NOT NULL primary key,
  report_timestamp timestamp without time zone NOT NULL,
  value_id         integer                     NOT NULL,
  text_value       character varying(255),
  numeric_value    double precision,
  bool_value       boolean,
  dt_value         timestamp with time zone,
  CONSTRAINT my_report_fkey_valdef FOREIGN KEY (value_id)
      REFERENCES public.my_value_defs (value_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE RESTRICT
);

它为每个值包含多个列,因为值可以是不同的数据类型。

value_id 外键:

CREATE TABLE my_value_defs
(
  value_id     serial                NOT NULL primary key,
  value_name   character varying(50) NOT NULL,
  data_type    integer               NOT NULL,
  CONSTRAINT my_value_defs_pkey PRIMARY KEY (value_id),
  CONSTRAINT my_value_defs_value_name_key UNIQUE (value_name)
);

现在我试图通过创建一个函数来缩短一个巨大的SQL查询,该函数可以返回 text_value numeric_value 或 bool_value 或 dt_value ,但发现必须显式定义返回的数据类型:

CREATE OR REPLACE FUNCTION public.rep_dta_val(
    val_id integer,
    dt timestamp with time zone,
    timeout integer)
  RETURNS timestamp with time zone AS -- ********** HERE **********
$BODY$SELECT
   r.dt_value
FROM
   my_report r
WHERE
   r.value_id = val_id
   AND r.report_timestamp BETWEEN
      dt - make_interval(secs := timeout)
      AND dt
ORDER BY
   r.report_timestamp desc
LIMIT 1;$BODY$
  LANGUAGE sql VOLATILE
  COST 100;

此功能尚未完成,请不要在此抱怨。

我不喜欢把所有的文字都转换成文字。应保留数据类型。

当无法创建一个输出数据类型可变的函数时,我必须在我的应用程序中生成一个完整的SQL查询,如下所示:

SELECT
(SELECT r.text_value    as acc_right     FROM my_report r WHERE r.value_id =  3 AND r.report_timestamp BETWEEN now() - INTERVAL '60 seconds' AND now() ORDER BY r.report_timestamp desc LIMIT 1),
(SELECT r.numeric_value as h_angle       FROM my_report r WHERE r.value_id =  4 AND r.report_timestamp BETWEEN now() - INTERVAL '60 seconds' AND now() ORDER BY r.report_timestamp desc LIMIT 1),
(SELECT r.text_value    as vol_flow      FROM my_report r WHERE r.value_id = 25 AND r.report_timestamp BETWEEN now() - INTERVAL '60 seconds' AND now() ORDER BY r.report_timestamp desc LIMIT 1),
(SELECT r.numeric_value as draft_mid     FROM my_report r WHERE r.value_id = 57 AND r.report_timestamp BETWEEN now() - INTERVAL '60 seconds' AND now() ORDER BY r.report_timestamp desc LIMIT 1),
(SELECT r.dt_value      as eta_timestamp FROM my_report r WHERE r.value_id = 58 AND r.report_timestamp BETWEEN now() - INTERVAL '60 seconds' AND now() ORDER BY r.report_timestamp desc LIMIT 1);

上面提到的查询正是我需要的,但是太长了,所以我创建了几个函数,每个数据类型对应一个函数,并按如下方式使用它们:

SELECT
rep_txt_val( 3, now(), 60) as acc_right,
rep_num_val( 4, now(), 60) as h_angle,
rep_txt_val(25, now(), 60) as vol_flow,
rep_num_val(57, now(), 60) as draft_mid,
rep_dta_val(58, now(), 60) as eta_timestamp;

现在我想有一个通用的函数来处理所有的数据类型。

EAV ?

我有一个完全动态的可配置软件,需要保存到表中的值的数量取决于这个软件的配置文件。我选择这个模型是为了简化数据库维护和系统间的数据传输。

另外,我可以说,不同的值应该以各自的间隔保存。

所以,我不会放弃 EAV公司

除了为遗留系统生成导出外,不需要将此垂直结构表示为水平数据行。这正是我的问题所在。

1 回复 | 直到 6 年前

klin 6 年前

使用 polymorphic function,

create or replace function func(anyelement)
returns anyelement language plpgsql as $$
begin
    raise notice '%', pg_typeof($1)::text;
    case pg_typeof($1)::text
        when 'text' then return 'some text';
        when 'numeric' then return 1.23;
        when 'timestamp without time zone' then return now();
        else return $1;
    end case;
end $$;

select func(null::text), func(null::numeric), func(null::timestamp)

NOTICE:  text
NOTICE:  numeric
NOTICE:  timestamp without time zone

   func    | func |            func            
-----------+------+----------------------------
 some text | 1.23 | 2018-10-02 14:51:51.407031
(1 row)

anyelement 作为确定返回类型的参数。