Mi texto como el siguiente

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

Se debe poder acceder como siguiendo

select 
  id, 
  reservationno, 
  supconfnumber,
  supitinerary 
from created_table

He usado la siguiente consulta, pero no obtengo el nombre de la columna.

select a[1], a[2], a[3]
from (
  select string_to_array('id=32702,reservationno=H11985W021216,supconfnumber=-,', ',')
) as dt(a)
1
Thusitha Indunil 22 jun. 2017 a las 11:48

3 respuestas

La mejor respuesta

Debe hacer algo como esto, pero el bloque DO devuelve nulo, por lo que no puede usarlo directamente de esta manera. Esto solo construye el texto de la consulta dinámicamente.

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;
$$  
0
JosMac 22 jun. 2017 a las 10:48

Prueba esto:

DO $$
DECLARE 
col1 TEXT;
col2 TEXT;
col3 TEXT;
s_sql TEXT;
BEGIN
select
substring(b.a1 from 0 for position('=' in b.a1)),
substring(b.a2 from 0 for position('=' in b.a2)),
substring(b.a3 from 0 for position('=' in b.a3)) 
INTO col1, col2, col3
from 
(
select a[1] as a1 , a[2] as a2, a[3] as a3 from  string_to_array('id=32702,reservationno=H11985W021216,supconfnumber=-,', ',') a 
) as b;

s_sql := ('select ' || col1 || ', ' || col2 || ', ' || col3 || ' from test');
EXECUTE(s_sql);
END $$;
0
Ohlsen1980 22 jun. 2017 a las 09:46

Prueba esto:

select
substring(b.a1 from 0 for position('=' in b.a1)) as column1,
substring(b.a2 from 0 for position('=' in b.a2)) as column2,
substring(b.a3 from 0 for position('=' in b.a3)) as column3
from 
(
select a[1] as a1 , a[2] as a2, a[3] as a3 from  string_to_array('id=32702,reservationno=H11985W021216,supconfnumber=-,', ',') a 
) as b
0
Ohlsen1980 22 jun. 2017 a las 09:23