Question

I m试图在表格中添加数据,使用插头功能或储存程序。然而,我要一劳永逸地插入多个记录。现在我都是VARCHAR,因此,我认为我可以使用诸如<代码>功能(名称VARCHAR,记录VARCHAR[]等功能。但是,当时我发现,普采克尔底土中存在着多层面的阵容支持。

This is what my function looks like at the moment. This doesn t produce the result I m looking for. When I ask

SELECT insert_data( tennis , ARRAY[ARRAY[ 1 , 2 ], ARRAY[ 3 , 4 ]])

我发现以下错误:

ERROR:  syntax error at or near "{"
LINE 1: INSERT INTO tennis VALUES (null, {{1}}), (null, {{3}});
                                         ^
QUERY:  INSERT INTO tennis VALUES (null, {{1}}), (null, {{3}});
CONTEXT:  PL/pgSQL function "insert_data" line 26 at EXECUTE statement

However I am expecting a query like

INSERT INTO tennis VALUES (null,  1 ,  2 ), (null,  3 ,  4 );

这是因为表网球有这种结构。

CREATE OR REPLACE FUNCTION insert_data (dsetname_in VARCHAR, records VARCHAR[][])
RETURNS BOOLEAN AS $PROC$
DECLARE
    insertquery TEXT;
    val VARCHAR;
    i INT;
    j INT;
BEGIN
    insertquery = $$INSERT INTO $$ || dsetname_in || $$ VALUES $$;
    FOR i IN array_lower(records, 1)..array_upper(records, 1)
    LOOP
        insertquery = insertquery || $$(null, $$;
        FOR j IN array_lower(records[i:i], 1)..array_upper(records[i:i], 1)
        LOOP
            val = records[i:i][j:j];
            insertquery = insertquery || val;
            IF j <> array_upper(records[i:i], 1) THEN
                insertquery = insertquery || $$, $$;
            END IF;
        END LOOP;
        insertquery = insertquery || $$)$$;
        IF i <> array_upper(records, 1) THEN
            insertquery = insertquery || $$, $$;
        END IF;
    END LOOP;
    insertquery = insertquery || $$;$$;
    EXECUTE insertquery;
    RETURN TRUE;
END;$PROC$ LANGUAGE  plpgsql ;

Answer 1

我怀疑这一整体做法的价值,因为我不认为这种做法增加了任何有用的抽象程度;但是,如果你必须这样做,而且你的所有价值观都是特征的,我认为,最清洁的做法是:

CREATE OR REPLACE FUNCTION insert_data(dsetname_in text, records text[])
RETURNS VOID LANGUAGE plpgsql AS $PROC$
DECLARE
  insertquery text;
  i int;
BEGIN
  insertquery :=  INSERT INTO   || dsetname_in ||   VALUES  ;
  FOR i IN array_lower(records, 1)..array_upper(records, 1)
  LOOP
    insertquery := insertquery ||  (null,   || records[i] ||  ), ;
  END LOOP;
  insertquery := left(insertquery, char_length(insertquery) - 1);
  EXECUTE insertquery;
END;
$PROC$;

那么,你可以把它称作这样,这似乎比你对nes阵列所显示的更清洁:

SELECT insert_data( tennis ,
                   ARRAY[$$ 1 , 2 $$,
                         $$ 3 , 4 $$]);

友情链接