主页 / dba / 问题 / 52826
Accepted
arthur
Asked: 2013-11-06 08:04:21 +0800 CST

将记录变量中的值插入表中

  • 772

我正在开发一个带有两个参数的用户定义函数：

create or replace function gesio(
    events_table_in regclass,  
    events_table_out regclass)
returns void as $$ ... $$

events_table_in并events_table_out具有完全相同的架构。

简单解释，我遍历 的记录events_table_in，操作记录并希望以下列方式将操作的记录附加（插入）到events_table_out：

OPEN recCurs FOR execute 
format('SELECT * FROM %s order by session_id, event_time', event_table_in);

LOOP
    FETCH recCurs into rec;
    if not found then
      exit;
    end if;

    -- 1. do something with rec

    -- 2. insert the rec into events_table_out

end loop;

我怎样才能保存rec成events_table_out？

postgresql insert

3 个回答

  1. Best Answer

    有一个仅使用 PL/pgSQL 的解决方案。简单优雅，也是。不过，相当先进的东西。
    需要 Postgres 9.0或更高版本（可能适用于旧版本）。

    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$;

    致电（重要！）：

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

    t并且t1是具有相同架构的表。

    anyelement仅当您需要它的值或数据类型用于函数体中的计算时，才需要多态参数 ( )。否则，您可以像后面的答案中演示的那样简化：

    主要成分

    要克服的一个障碍是函数内部的变量不能定义为多态类型anyelement（还）。这个关于 SO 的相关答案解释了解决方案。也为旧版本提供了解决方法。

    我正在提交一个NULLtype 的值t，它有三个目的：

    • 提供表名。
    • 提供表格类型。
    • 作为循环变量。

    第一个参数的值被丢弃。只需使用NULL.

    考虑更多关于 SO 的相关答案。最有趣的部分是最后一章“各种完整的表格类型”。

    db<>fiddle here
    旧sqlfiddle

    如果您的计算不是太复杂，您可以将循环替换为单个动态 SQL 语句，这通常更快。

    • 11

  2. RECORD不幸的是，使用 PL/pgSQL解析类型并不容易。如果传入参数的表的结构始终与其他表或类型相同，则可以直接使用此类型而不是RECORD，然后使用以下内容：

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

    但这不适用于RECORD类型。我能想到的唯一解决方案是手动创建查询。但是 PL/pgSQL 无法动态获取RECORD类型的键。所以你必须使用一些外部工具。这种工作最好的（在我看来）是hstore扩展。安装后，您可以在数据库上创建它（以下仅适用于 9.1+，因为之前您应该手动完成）：

    CREATE EXTENSION hstore;

    现在。您可以使用 将RECORD类型转换为hstore类型，hstore(recCurs)因此您可以使用以下函数动态迭代其键和值each：

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

    events_table_out当然，只有当“指向”的表具有所有列events_table_in（第一个可以有更多列）时，它才会起作用。

    RESUMING：总是希望在 PL/pgSQL 上使用一些动态键/值数据类型，RECORD但还不够，hstore可以使用。

    • 5

  3. 我需要做类似的事情，最终使用了 SELECT 中的 INSERT。我需要在同一张表中复制记录，但要更改一个字段。也许这不是最优雅的解决方案，但它确实有效。

    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$

    然后，我这样调用函数：

    select duplicar_mascotas (1,2);

    这会将记录从 id = 1 的人复制到 id = 2 的人。

    • 0

相关问题