Help creating a Dynamic Insert in PostgreSQL

Question

Help creating a Dynamic Insert in PostgreSQL

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have a table Juego which has 4 columns nombre, tipo, autor, proveedor , the function that I create allows me to enter data filtering according to characters correctly, but I would like to write the columns and the value of the array that I should enter in said column , considering that this would not be practical if I had many columns, I would like to know if someone could help me how to perform this function dynamically since I have not yet had the solution.

Example of how the function you create works

Data:

halo*accion*juan*pedro|gow*accion*juan*gabriel|the last of us*aventura*lucius*jose

The filtering function as follows

nombre  | tipo  | autor  | proveedor
halo    |accion | juan   | gabriel
gow     |accion | juan   | gabriel
tlou    |aventur|lucius  | jose

Table

create table juego(nombre varchar(60),tipo varchar(60),autor varchar(60), proveedor text);

Function created

CREATE OR REPLACE FUNCTION insrt(var text)   
RETURNS integer AS $BODY$
        DECLARE
           fila text[];
           row text;
           colm text[];       
        BEGIN   
fila := regexp_split_to_array(var, E'\|');     
FOREACH row IN ARRAY fila   
LOOP        
colm := regexp_split_to_array(row, E'\*');         
insert into juego values(colm[1],colm[2],colm[3],colm[4]);

END LOOP;   
RETURN 1;

END;

sql bucle-for postgresql consulta-sql postgresql-9.3

asked by FJAL 13.07.2018 в 02:55

source

2 answers

Pass a class dynamically in an Intent object JQuery: Multiplication of data by position in a table

score 0 · Answer 1

To be dynamic, you must concatenate the values of your colm array into a variable and then execute that variable with the EXECUTE command. This would be the function, add comments for you to understand:

CREATE OR REPLACE FUNCTION insrt(var text)   
RETURNS integer AS $BODY$
DECLARE
     fila text[];
     row text;
     /*para el loop de colm*/
     row2 text;
     colm text[];
     /* para guardar los valores del array colm*/
     concatena text := '';
     /* saber saber la ultima vuelta del loop de colm*/
     num NUMERIC :=0;
BEGIN   
fila := regexp_split_to_array(var, E'\|');     
FOREACH row IN ARRAY fila   
LOOP        
colm := regexp_split_to_array(row, E'\*'); 

/*loop para rescatar los valores de colm*/
FOREACH row2 IN ARRAY colm   
LOOP
  /*incremento la variable*/
    num := num + 1;
    /*compruebo si es la ultima vuelta, con array_length rescato el tamaño del array*/
    IF array_length(colm,1) <> num THEN
      /*si no es la ultima concateno a la variable, el valor dentro de comillas, mas una coma "'valor',"*/
        concatena := concatena || '''' || row2 || ''',';
    ELSE
    /* si es la ultima vuelta solo concateno el valor dentro de comillas "'valor'"*/
        concatena := concatena || '''' || row2 || '''';
    END IF;
END LOOP;

/*una vez rescatado los valores de colm, concateno con el insert y lo ejecuto*/
EXECUTE 'insert into juego values(' || concatena || ')';
/*limpio las variables para que queden disponibles en caso de ocuparlas nuevamente*/
concatena := '';
num :=0;

END LOOP;   
RETURN 1;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100

I hope it serves you, regards

score 0 · Answer 2

You can try:

create or replace function insrt(string text) returns void as $$
declare
  idx2 int2;
  row1 text;
  row2 text;
  col_list text[];
  idx1 int2 := 0;
  _sql text := 'values';
  val_list text[] := regexp_split_to_array(string, E'\|');
begin
  foreach row1 in array val_list loop
    idx2 := 0;
    idx1 := idx1 + 1;
    _sql := _sql || '(';
    col_list := regexp_split_to_array(row1, E'\*');

    foreach row2 in array col_list loop
      idx2 := idx2 + 1;

      if idx2 = array_length(col_list, 1) then
        _sql := _sql || quote_literal(row2);
      else
        _sql := _sql || quote_literal(row2) || ',';
      end if;
    end loop;

    if idx1 = array_length(val_list, 1) then
      _sql := _sql || ')';
    else
      _sql := _sql || '),';
    end if;
  end loop;

  execute 'insert into juego ' || _sql;
end;
$$ language plpgsql;