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

Question

我的文字如下

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

Answer 1

你必须做这样的事情，但是 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;
$$