Блог

Главная - Вопросы по программированию - Приведите столбцы VARCHAR к значениям int, bigint, time и т. Д. (PL/pgSQL)

Приведите столбцы VARCHAR к значениям int, bigint, time и т. Д. (PL/pgSQL)

#sql #postgresql #plpgsql

Вопрос:

Проблема

(Это для библиотеки аналитики с открытым исходным кодом.)

Вот результаты нашего запроса от events_view :

   id  | visit_id |  name  |             prop0          | prop1 | url
------ ---------- -------- ---------------------------- ------- ------------
 2004 |        4 | Magnus | 2021-10-26 02:25:55.790999 | 142   | cnn.com
 2007 |        4 | Hartis | 2021-10-26 02:26:37.773999 | 25    | fox.com

В настоящее время все столбцы находятся VARCHAR .

   Column  |        Type       | Collation | Nullable | Default
---------- ------------------- ----------- ---------- ---------
 id       | bigint            |           |          |
 visit_id | character varying |           |          |
 name     | character varying |           |          |
 prop0    | character varying |           |          |
 prop1    | character varying |           |          |
 url      | character varying |           |          |

Они должны быть чем-то вроде

   Column  |           Type         | Collation | Nullable | Default
---------- ------------------------ ----------- ---------- ---------
 id       | bigint                 |           |          |
 visit_id | bigint                 |           |          |
 name     | character varying      |           |          |
 prop0    | time without time zone |           |          |
 prop1    | bigint                 |           |          |
 url      | character varying      |           |          |

Желаемый результат

Жесткое кодирование этих приведений, как в SELECT visit::bigint, name::varchar, prop0::time, prop1::integer, url::varchar FROM tbl , не подходит, имена столбцов известны только во время выполнения.

Для упрощения мы могли бы разбить каждый столбец только на три типа: логический, числовой или переменный. Используйте регулярные выражения ниже для сопоставления типов:

  • логический: ^(true|false|t|f)$
  • числовой: ^(,-)[0-9] (,.[0-9] )$
  • varchar: каждый результат, который не соответствует логическому и числовому выше

Каким должен быть SQL, который определяет, к какому типу относится каждый столбец, и динамически приводит их?

Комментарии:

1. Не связано: вы действительно используете PostgreSQL 9.4? Он достиг ЭОЛА давным-давно.

2. Что делать, если столбец содержит несколько различных обнаруженных типов данных?

3. @jarlh мы сопоставим регулярное выражение для идентификации, чтобы упростить использование первого ненулевого значения столбца.

Ответ №1:

Это несколько идей, а не истинное решение для этой сложной работы. Вместо регулярных выражений можно использовать медленную, но очень надежную функцию.

 create or replace function can_cast(s text, vtype text)
returns boolean language plpgsql immutable as 
$body$
begin
    execute format('select %L::%s', s, vtype);
    return true;
exception when others then
    return false;
end;
$body$;

Данные могут быть представлены следующим образом (частичный список столбцов из вашего примера):

 create or replace temporary view tv(id, visit_id, prop0, prop1) as
values
(
    2004::bigint,
    4::bigint, 
    case when can_cast('2021-10-26 02:25:55.790999', 'time') then '2021-10-26 02:25:55.790999'::time end,
    case when can_cast('142', 'bigint') then '142'::bigint end
), -- determine the types
(2007, 4, '2021-10-26 02:26:37.773999', 25)
-- the rest of the data here;

Я считаю, что также возможно динамически генерировать DDL временного представления как select от events_view .

Комментарии:

1. Хорошее понимание, я нашел решение для своей проблемы, создав для каждого атрибута/столбца таблицу с типами кандидатов, а затем выбрав те, которые имеют наиболее допустимые значения. Довольно сложно и медленно, но работает.

Метки: , ,