CODEWITHSUNDEEP
arrayspostgresqldynamicinsertplpgsql
Reneger
Reneger

Reputation: 81

PostgreSQL - Dynamic Tables, insert values from Text array in a function

A client wants to store anything in seperate tables. (Long story, it's nescessary). To do this, i've build an Postgres function to create new tables on the fly in it's own namespace.

These tables can have 2, 4, or 100 columns, just what the user wants. No problem, this works. The used datatypes in these dynamic tables are native, e.g. text, booleans, integers, etcetera.

Now comes the problem, i've got to insert data into these tables. The point is, the users can not access the tables directly, they'll do this through a function.

For a couple of datatypes is this not a problem, but for the text datatypes it's problematic.

Here is the function till now:

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

The table ito_fields stores all the field metadata, so the datatype, versions, descriptions are stored here. the ito table stores all the dynamic table data.

The point in this function are the quotes. The insert function is created dynamicly, therefore i've got to add some quotes around the text fields in the insert function. Postgres is giving errors as soon as i do that. Even with the quote_literal functions it's still a problem, because of the string concatenation (i know, security risks, but that's no problem for now).

I've tried to use quote_literal, quote_ident, even replacing the quotes (') with a replacement, until the execute function (replace(query, l_quote_rep, '''').. I really don't have a clue now how to fix this...

Thanks in advance.

Upvotes: 1

Views: 1974

Answers (1)

filiprem
filiprem

Reputation: 7124

Arrays, aggregates, quote_ident and quote_nullable are your friends.

This should work, and code is shorter:

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

Upvotes: 1

Related Questions