sql - 如何在PostgreSQL中以逗号分隔的文本转换为具有列名的表

我的文字如下

'id=32702, reservationno=H11985W021216, supconfnumber=-, supitinerary=,'

它应该可以像下面这样访问

select
  id,
  reservationno,
  supconfnumber,
  supitinerary
from created_table

我使用了以下查询，但它没有得到列名。

select a[1], a[2], a[3]
from (
  select string_to_array('id=32702,reservationno=H11985W021216,supconfnumber=-,', ',')
) as dt(a)

最佳答案

您必须这样做，但是do block返回void，因此不能直接使用它。这只动态构造查询的文本。

do $$
declare
    s_sql_start text := 'select ';
    s_sql text := s_sql_start;
    _rec record;
begin
    for _rec in (
        select * from (
            select
            substr(splitedtext, 0, position('=' in splitedtext)) as column_name,
            substr(splitedtext, position('=' in splitedtext)+1) as column_value
            from (
                select trim(regexp_split_to_table(mytext, ',')) as splitedtext
                from (
                select 'id=32702, reservationno=H11985W021216, supconfnumber=-, supitinerary=,'::text as mytext
                ) src
            ) spl
        ) filtered where nullif(column_name,'') is not null)
    loop
        if s_sql<> s_sql_start then
            s_sql := s_sql||', ';
        end if;
        s_sql:=s_sql ||quote_nullable(_rec.column_value)||' as '||_rec.column_name;
    end loop;
    raise notice '%',s_sql;
    --will only show query text, DO block returns void so you cannot see results from in
end;
$$

关于sql - 如何在PostgreSQL中以逗号分隔的文本转换为具有列名的表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44694540/