Перебрать столбцы записи

Мне нужно перебрать тип RECORD элементы по ключу / индексу, как я могу сделать это, используя структуры массива в других языках программирования.

Например:

DECLARE
    data1    record;
    data2    text;
...
BEGIN
...
FOR data1 IN
    SELECT
        *
    FROM
        sometable
LOOP

    FOR data2 IN
        SELECT
            unnest( data1 )   -- THIS IS DOESN'T WORK!
    LOOP
        RETURN NEXT data1[data2];   -- SMTH LIKE THIS
    END LOOP;

END LOOP;
Источник

7 ответов

Решение

Как объяснил @Pavel, невозможно просто пройти запись, как вы можете пройти массив. Но есть несколько способов обойти это - в зависимости от ваших точных требований. В конечном итоге, поскольку вы хотите вернуть все значения в одном столбце, вам необходимо привести их к одному и тому же типу - text это очевидный общий язык, потому что для каждого типа есть текстовое представление.

Быстро и грязно

Скажем, у вас есть стол с integer, text и date колонка.

CREATE TEMP TABLE tbl(a int, b text, c date);
INSERT INTO tbl VALUES
 (1, '1text',     '2012-10-01')
,(2, '2text',     '2012-10-02')
,(3, ',3,ex,',    '2012-10-03')  -- text with commas
,(4, '",4,"ex,"', '2012-10-04')  -- text with commas and double quotes

Тогда решение может быть простым, как:

SELECT unnest(string_to_array(trim(t::text, '()'), ','))
FROM   tbl t;

Работает для первых двух строк, но не для особых случаев 3 и 4 строки.
Вы можете легко решить проблему с запятыми в текстовом представлении:

SELECT unnest(('{' || trim(t::text, '()') || '}')::text[])
FROM   tbl t
WHERE  a < 4;

Это будет работать нормально - за исключением строки 4, которая имеет двойные кавычки в текстовом представлении. Те избежали удвоения их. Но конструктор массива должен был бы избежать их \, Не уверен, почему эта несовместимость есть...

SELECT ('{' || trim(t::text, '()') || '}') FROM tbl t WHERE a = 4

Урожайность:

{4,""",4,""ex,""",2012-10-04}

Но вам понадобится:

SELECT '{4,"\",4,\"ex,\"",2012-10-04}'::text[];  -- works

Правильное решение

Если бы вы знали имена столбцов заранее, чистое решение было бы простым:

SELECT unnest(ARRAY[a::text,b::text,c::text])
FROM tbl

Поскольку вы работаете с записями общеизвестного типа, вы можете просто запросить системный каталог:

SELECT string_agg(a.attname || '::text', ',' ORDER  BY a.attnum)
FROM   pg_catalog.pg_attribute a 
WHERE  a.attrelid = 'tbl'::regclass
AND    a.attnum > 0
AND    a.attisdropped = FALSE

Поместите это в функцию с динамическим SQL:

CREATE OR REPLACE FUNCTION unnest_table(_tbl text)
  RETURNS SETOF text LANGUAGE plpgsql AS
$func$
BEGIN

RETURN QUERY EXECUTE '
SELECT unnest(ARRAY[' || (
    SELECT string_agg(a.attname || '::text', ',' ORDER  BY a.attnum)
    FROM   pg_catalog.pg_attribute a 
    WHERE  a.attrelid = _tbl::regclass
    AND    a.attnum > 0
    AND    a.attisdropped = false
    ) || '])
FROM   ' || _tbl::regclass;

END
$func$;

Вызов:

SELECT unnest_table('tbl') AS val

Возвращает:

val
-----
1
1text
2012-10-01
2
2text
2012-10-02
3
,3,ex,
2012-10-03
4
",4,"ex,"
2012-10-04

Это работает без установки дополнительных модулей. Другой вариант - установить расширение hstore и использовать его, как демонстрирует @Craig.

Источник

PL/pgSQL не предназначен для того, что вы хотите делать. Он не считает запись итеративной, это кортеж, возможно, разных и несовместимых типов данных.

PL/pgSQL имеет EXECUTE для динамического SQL, но EXECUTE запросы не могут ссылаться на переменные PL/pgSQL, такие как NEW или другие записи напрямую.

Что вы можете сделать, это преобразовать запись в hstore структура ключ / значение, затем переберите hstore, использование each(hstore(the_record)), который производит набор строк key,value кортежи. Все значения приведены к их text представления.

Эта игрушечная функция демонстрирует итерацию по записи, создавая анонимную ROW(..) - которые будут иметь имена столбцов f1, f2, f3 - затем преобразовать это в hstoreперебирая пары столбец / значение и возвращая каждую пару.

CREATE EXTENSION hstore;

CREATE OR REPLACE FUNCTION hs_demo()
RETURNS TABLE ("key" text, "value" text)
LANGUAGE plpgsql AS
$$
DECLARE
  data1 record;
  hs_row record;
BEGIN
  data1 = ROW(1, 2, 'test');
  FOR hs_row IN SELECT kv."key", kv."value" FROM each(hstore(data1)) kv
  LOOP
    "key" = hs_row."key";
    "value" = hs_row."value";
    RETURN NEXT;
  END LOOP;
END;
$$;

На самом деле вы бы никогда не написали это так, поскольку весь цикл можно заменить простым RETURN QUERY заявление и делает то же самое each(hstore) в любом случае - так что это только для того, чтобы показать, как each(hstore(record)) работает, и вышеупомянутая функция никогда не должна использоваться.

Источник

Эта функция не поддерживается в plpgsql - запись НЕ является хеш-массивом, как другие языки сценариев - она ​​похожа на C или ADA, где эта функциональность невозможна. Вы можете использовать другой язык PL, такой как PLPerl или PLPython, или некоторые хитрости - вы можете выполнять итерации с типом данных (расширением) HSTORE или с помощью динамического SQL

см. Как установить значение поля составной переменной с использованием динамического SQL

Но запрос на эту функциональность обычно означает, что вы делаете что-то не так. Когда вы используете PL/pgSQL, вы думаете по-другому, чем вы используете Javascript или Python

Источник

Я не смог найти правильный способ зацикливания записи, поэтому я сначала преобразовал запись в json и зациклил json.

      declare
    _src_schema varchar := 'db_utility';
    _targetjson json;
    _key   text;
    _value text;
BEGIN
    select row_to_json(c.*) from information_schema.columns c where c.table_name = prm_table and c.column_name = prm_column
        and c.table_schema = _src_schema into _targetjson;
        raise notice '_targetjson %', _targetjson;

        FOR _key, _value IN
        SELECT * FROM jsonb_each_text(_targetjson)
        LOOP
        -- do some math operation on its corresponding value
            RAISE NOTICE '%: %', _key, _value;
        END LOOP;
    return true;
end;
Источник 
FOR data2 IN
    SELECT d
    from  unnest( data1 ) s(d)
LOOP
    RETURN NEXT data2;
END LOOP;
Источник

Если вы закажете свои результаты до зацикливания, вы добьетесь того, чего хотите.

for rc in select * from t1 order by t1.key asc loop
 return next rc;
end loop;

будет делать именно то, что вам нужно. Это также самый быстрый способ выполнить такую ​​задачу.

Источник

Прошло уже более 10 лет, и до сих пор нет возможности динамически перебирать столбцы записей. Но у меня есть решение, которое немного помогает. Следующая функция создаст и вернет определения записей в объекте json.

      CREATE OR REPLACE FUNCTION transactions.f_get_record_definitions(p_txt_sql text)
 RETURNS json
 LANGUAGE plpgsql
AS $function$
declare
    j_rec_defs json;
    rec record;
    rec2 record;
    txt_type text;
    txt_j_build text;
begin
    execute p_txt_sql limit 1 into rec;
    txt_j_build := '{';
    j_rec_defs := row_to_json(rec)::json;
    for rec2 in
        select * from json_each(j_rec_defs)
    loop
        case json_typeof(rec2.value::json)
        when 'object' then txt_type := 'json';
        when 'string' then txt_type := 'text';
        when 'array' then txt_type := 'array';
        when 'boolean' then txt_type := 'bool';
        when 'number' then txt_type := 'numeric';
        else
            txt_type := 'json';
        end case;
        txt_j_build := txt_j_build||'"'||rec2.key||'":"'||txt_type||'",';
    end loop;
    txt_j_build := left(txt_j_build, -1)||'}';
    return txt_j_build::json;
end;
$function$
;

Ну, вы можете использовать только типы json или jsonb, но во многих случаях это нормально. Если кто-то знает, как получить типы напрямую из записи с помощью pg_type, было бы здорово.

Я действительно не знаю, решит ли это первоначальную проблему, но с помощью этой функции вы сможете создавать списки типов для динамической возвращаемой записи или набора записей в других функциях. Если вам не нравятся определения, вы также можете выполнить в цикле простой «return next Rec2» вместо определения типов.

Источник
Другие вопросы по тегам