Help creating a function in PostgreSQL that inserts datps in columns with a delimiter character

Question

Help creating a function in PostgreSQL that inserts datps in columns with a delimiter character

Navigation

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

0

I have a table with characters Name, Aepllido, Cedula and Date of Birth, and I receive a series of sentences similar to

Luis*Lopez*273982*24/12/1998|Juan*Gonzalez*232234*03/12/1987|Julio*Perez*274234*03/12/1995

I want to be able to delimit the insert using two special characters, where the | is the line break and the * the insertion between columns

I managed to solve the line break between tables using this query

SELECT regexp_split_to_table('Luis*Lopez*273982*24/12/1998|Juan*Gonzalez*232234*03/12/1987|Julio*Perez*274234*03/12/1995', '\|')

But I still can not think of how to create the function that separates through * and insert in the columns, Thanks in advance to those who can help

sql php codeigniter postgresql

asked by FJAL 10.07.2018 в 17:57

source

2 answers

0

If from PHP you can make a combination of explode - explode - implode .

You will need to do a first explode using | for each row and then a explode using * for each column.

Then, with the array of columns you can do implode to represent them separated by , for example, an optimal format for insertion in database for example.

Here's how it would be obtained:

$str="Luis*Lopez*273982*24/12/1998|Juan*Gonzalez*232234*03/12/1987|Julio*Perez*274234*03/12/1995";
$arrFilas=explode("|",$str);
foreach ($arrFilas as $row){
    $arrColumns=explode("*",$row);
    echo implode(',',$arrColumns).PHP_EOL;
}

The result would be:

Luis,Lopez,273982,24/12/1998
Juan,Gonzalez,232234,03/12/1987
Julio,Perez,274234,03/12/1995

As you see, it shows you each row and then each column separated by commas.

answered by 10.07.2018 в 19:47

Hibernate Mapping Error My device can not find other Bluetooth devices with startDiscovery ();

score 0 · Accepted Answer

create table PERSONA(nombre varchar(50),apellido varchar(50),id varchar(50),fecha text);

CREATE OR REPLACE FUNCTION insertar_comlumnas(entrada text) RETURNS int AS 
$$
    DECLARE
       fila RECORD;
       f_persona PERSONA%ROWTYPE;
       columnas text[];
       i_sql text;
    BEGIN
       FOR fila IN  SELECT regexp_split_to_table(entrada, '\|') LOOP
              select regexp_split_to_array(fila::text, E'\*') into columnas;
              f_persona.nombre:=columnas[1];
              f_persona.apellido:=columnas[2];
              f_persona.id:=columnas[3];
              f_persona.fecha:=columnas[4];
              select 'INSERT INTO PERSONA VALUES($1.*) ' into i_sql;
              EXECUTE format(i_sql) USING f_persona;
       END LOOP;

        RETURN 1;
    END;
    $$ LANGUAGE plpgsql;