PostgreSQL -动态表格，在函数中插入文本数组中的值

Question

客户端希望将任何内容存储在单独的表中。(说来话长，这是必然的)。为此，我构建了一个Postgres函数，以便在它自己的名称空间中动态创建新表。

这些表可以有2、4或100列，这正是用户想要的。没问题，这行得通。这些动态表中使用的数据类型是原生的，例如文本、布尔值、整数等等。

现在问题来了，我必须将数据插入到这些表中。关键是，用户不能直接访问这些表，他们将通过一个函数来访问。

对于一些数据类型，这不是问题，但对于文本数据类型，这是有问题的。

下面是到目前为止的函数：

-- Function: add(integer, text[])

-- DROP FUNCTION add(integer, text[]);

CREATE OR REPLACE FUNCTION add(id integer, fields text[])
  RETURNS integer AS
$BODY$
DECLARE
l_line_ending text := ')';
  l_fieldtype integer;
  l_ito_table_name text;
  l_ito_fieldnames text;
  l_field ito_fields%rowtype;
  l_first_loop boolean := true;
  l_values_to_insert text := 'VALUES (';
  l_loop_counter integer := 0;
  l_query text;

BEGIN
  select into l_ito_table_name ito_table_name from ito where id = target_ito_id;
  l_ito_fieldnames := 'insert into ' || l_ito_table_name || '(';
    FOR l_field IN SELECT * FROM ito_fields
    WHERE ito_fields.ito_id = target_ito_id
    order by ito_fields.id asc
    LOOP
        l_loop_counter := l_loop_counter +1;
        l_fieldtype := l_field.fieldtype;
        if not l_first_loop THEN
            l_values_to_insert := (l_values_to_insert || ', ');
        end if;
        if l_field.fieldtype = 1 THEN
             l_values_to_insert := (l_values_to_insert || '''' || (fields[l_loop_counter]) || '''' );
        elsif l_field.fieldtype = 2 THEN
            l_values_to_insert := quote_literal(l_values_to_insert || private.cast_to_integer(fields[l_loop_counter]));
        elsif l_field.fieldtype = 3 THEN
            l_values_to_insert := quote_literal(l_values_to_insert || private.cast_to_boolean(fields[l_loop_counter]));
        elsif l_field.fieldtype = 4 THEN
            l_values_to_insert := quote_literal(l_values_to_insert || private.cast_to_float(fields[l_loop_counter]));
        else 
            return 103;
        end if;
        if l_first_loop then
            l_ito_fieldnames := l_ito_fieldnames || l_field.column_name;
            l_first_loop := false;
        else
            l_ito_fieldnames := l_ito_fieldnames || ', ' || l_field.column_name;
        end if;
    END LOOP;
    l_ito_fieldnames := l_ito_fieldnames || l_line_ending;
    l_values_to_insert := ((l_values_to_insert) || (l_line_ending));
    l_query := (l_ito_fieldnames || l_values_to_insert);
    EXECUTE l_query;
  return 0;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION add(integer, text[])
  OWNER TO postgres;

表ito_fields存储所有字段元数据，因此数据类型、版本、描述都存储在这里。ito表存储所有的动态表数据。

这个函数的要点是引号。插入函数是动态创建的，因此我必须在插入函数中的文本字段两边添加一些引号。我一这样做，Postgres就会给出错误。即使使用quote_literal函数，这仍然是一个问题，因为字符串连接(我知道有安全风险，但目前没有问题)。

我尝试使用查询，quote_ident，甚至用替换来替换引号(')，直到execute函数(replace( quote_literal，l_quote_rep，‘)..我现在真的不知道怎么解决这个问题...

提前谢谢。

Answer 1

Arrays，aggregates，quote_ident and quote_nullable是你的朋友。

这应该是可行的，并且代码更短：

CREATE OR REPLACE FUNCTION add(id integer, fields text[])
  RETURNS integer AS
$BODY$
DECLARE
  l_ito_table_name text;
  l_query text;
  l_fields text;
  r_values text;
BEGIN
    --get table name
    SELECT INTO l_ito_table_name quote_ident(ito_table_name) FROM ito WHERE id = target_ito_id;
    -- get column names
    SELECT INTO l_fields
    array_to_string( array_agg(quote_ident(column_name)), ',' )
    FROM ito_fields
    WHERE ito_fields.ito_id = target_ito_id
    order by ito_fields.id asc;
    -- prepare values
    SELECT INTO r_values
    array_to_string( array_agg(quote_nullable(u.name)), ',' )
    FROM unnest(fields) u(name);
    l_query := 'insert into ' || l_ito_table_name || '(' || l_fields || ') values (' || r_values || ')';
    EXECUTE l_query;
    return 0; -- why 0?
END;
$BODY$;