时间戳

首页 > 解决方案 > 具有松散返回数据类型的 PostgreSQL 函数

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

问题描述

这是包含数据的主表：

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外键链接的第二个表的“data_type”列：

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)
);

现在我试图通过创建一个可以返回text_valueornumeric_value或bool_valueor的函数来缩短一个巨大的 SQL 查询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;

现在我想为所有数据类型提供一个通用函数。

PS 为什么使用EAV？

因为它以前是一个传统的表，这是一场噩梦，因为需要不时地自动创建列。有时，常规用户无权执行 DDL 命令。

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

另外我可以说各种值应该保存在它们自己的间隔中。

所以，我不会放弃EAV，因为它已经比以前的常规表更有效了。

除了为遗留系统生成导出外，无需将这种垂直结构表示为水平数据行。这正是我的问题的主题。

标签： postgresqlfunctionplpgsqlentity-attribute-value

解决方案

使用多态函数，例如：

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作为参数来确定返回类型。

推荐阅读