Inserir valores de uma variável de registro em uma tabela

Existe uma solução apenas com PL/pgSQL. Simples e elegante também. Coisas bem avançadas, no entanto.
Requer Postgres 9.0 ou posterior (possível solução alternativa para versões mais antigas).

CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR _tbl_in IN EXECUTE
      format('SELECT * FROM %s', pg_typeof(_tbl_in))
   LOOP
      -- do something with record

      EXECUTE format('INSERT INTO %s SELECT $1.*', _tbl_out)
      USING _tbl_in;
   END LOOP;
END
$func$;

Ligue (importante!):

SELECT gesio(NULL::t, 't1');

te t1sendo tabelas com esquema idêntico.

O parâmetro polimórfico ( anyelement) só é necessário se você precisar de seu valor ou tipo de dados para o cálculo no corpo da função. Caso contrário, você pode simplificar como demonstrado nesta resposta posterior:

• Inserir valores de uma variável de registro em uma tabela de subclasse

Ingredientes principais

• Cursor implícito de um FORloop em vez de cursor explícito. Isso é geralmente preferível.

• Tipos polimórficos

• Tipos de identificador de objeto

• SQL dinâmico em plpgsql

• VALUESpode receber um tipo de linha diretamente.

Um obstáculo a ser superado é que variáveis ​​dentro da função não podem ser definidas como do tipo polimórfico anyelement(ainda). Esta resposta relacionada no SO explica a solução. Fornece uma solução alternativa para versões mais antigas também.

Estou entregando um NULLvalor de type t, que serve a três propósitos:

• Forneça o nome da tabela.
• Forneça o tipo de tabela.
• Servir como variável de loop.

O valor do primeiro parâmetro é descartado. Basta usar NULL.

Considere esta resposta relacionada no SO com mais detalhes . A parte mais interessante é o último capítulo "Vários tipos de tabelas completas" .

db<>fiddle aqui
_{Velho sqlfiddle}

Se seus cálculos não forem muito sofisticados, você poderá substituir o loop por uma única instrução SQL dinâmica, que normalmente é mais rápida.

Infelizmente não é fácil analisar o RECORDtipo usando PL/pgSQL. Se a estrutura das tabelas passadas nos argumentos for sempre a mesma de alguma outra tabela ou tipo, você poderá usar esse tipo diretamente em vez de RECORDe, em seguida, usar o seguinte:

DECLARE
    recCurs table_or_type;
...
BEGIN
...
OPEN recCurs FOR EXECUTE ...
...
EXECUTE 'INSERT INTO ' || events_table_out || ' VALUES(($1).*)'
        USING recCurs;
...

Mas isso não funcionará com o RECORDtipo. A única solução que consigo pensar é criar a consulta manualmente. Mas o PL/pgSQL não permite obter dinamicamente as chaves de um RECORDtipo. Então você tem que usar algumas ferramentas externas. O melhor (na minha opinião) para esse tipo de trabalho é a hstoreextensão . Uma vez instalado, você pode criá-lo em seu banco de dados (o seguinte funciona apenas na versão 9.1+, antes você deve fazê-lo manualmente):

CREATE EXTENSION hstore;

Agora. Você pode converter um RECORDtipo em um hstoretipo, usando hstore(recCurs), e assim você pode iterar dinamicamente sobre suas chaves e valores com a eachfunção:

DECLARE
   recCurs record;
   kv record;
   v_cols text;
   v_vals text;
BEGIN
...
    OPEN recCurs FOR EXECUTE ...
...
    -- 1. do something with rec

    -- 2. insert the rec into events_table_out:
    v_cols := '';
    v_vals := '';
    FOR kv IN SELECT * FROM each(hstore(recCurs)) LOOP
        v_cols := v_cols || kv.key || ',';
        v_vals := v_vals || quote_nullable(kv.value) || ',';
    END LOOP;
    v_cols := substr(v_cols, 1, length(v_cols)-1);
    v_vals := substr(v_vals, 1, length(v_vals)-1);
    EXECUTE 'INSERT INTO ' || events_table_out
            || '(' || v_cols || ') '
            || 'VALUES (' || v_vals || ')';
...

Claro, só funcionará se a tabela "apontada" por events_table_outtiver todas as colunas que events_table_inpossui (a primeira pode ter mais colunas).

RESUMINDO: sempre que você quiser algum tipo de dado chave/valor dinâmico em PL/pgSQL, e RECORDnão é suficiente, hstorepode ser usado.

Eu precisava fazer algo parecido e acabei usando o INSERT do SELECT. Preciso duplicar registros na mesma tabela mas alterando um campo. Talvez não seja a solução mais elegante, mas funciona.

CREATE OR REPLACE FUNCTION public.duplicar_mascotas(
    origen integer DEFAULT 0, 
    destino integer DEFAULT 0
)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
  fila_mascota record;
  nvo_id_mascota BIGINT;
  contador integer;
  cur_mascotas cursor for
    SELECT id FROM public.mascotas WHERE id_persona = origen;
BEGIN
    contador := 0;
    raise notice 'hola';
    open cur_mascotas;
    loop
        fetch cur_mascotas into fila_mascota;
        exit when not found;
        -- insert del select
        INSERT INTO public.mascotas ( nombre, id_persona, edad_meses) 
        SELECT nombre, destino as id_persona, edad_meses
        FROM public.mascotas
        WHERE 
            id = fila_mascota.id;
        contador := contador + 1;
    end loop;
    return contador;
END;
$function$

Então, eu chamo a função assim:

select duplicar_mascotas (1,2);

Isso duplica os registros da pessoa com id = 1 para a pessoa com id = 2.