SQL string combination

4

I need to generate all word combinations of a varchar of a record from a database.

I need to develop in MySQL in PostgreSQL

To get simpler:

BASE DATA TABLE

Howtomakeaprocedure/functionthatselecttherecordsanddothis:

COMBINEDTABLE

That is, detected 3 words, made the combination of 3 factorial of these, generating an FK referenced to the ID (PK)

Considering that the TABLE DATA_BASE has N lines and that it has a string of n words, I'm already considering that it will have a large number of lines generated in the new table.

So, what would be the best way to execute this "query" that can be done on a cloud server (eg scheduling execution) so that if it stops, it will be resumed from the point where it was executed? / p>     

asked by anonymous 11.08.2016 / 19:25

1 answer

-1

Here is a skeleton of a Function in PostgreSQL :

create type RET_TEXTO as (
  RIdentificador numeric(18,0),
  RTexto varchar(500)
);


create or replace function MONTAR_TEXTO(
) returns setof RET_TEXTO as $$
declare
  Dcl_TextoID numeric(18,0);
  Dcl_TextoText varchar(500);

  MyCur refcursor;

  result RET_TEXTO;
begin

  open MyCur for select ID, tx_texto from tb_texto ;
  loop
    fetch MyCur into Dcl_TextoID, Dcl_TextoText;    
    exit when (not found);

    result.RIdentificador := Dcl_TextoID;
    result.RTexto := Dcl_TextoText;

    /*Implementar aqui a Lógica */


    return next result;

  end loop;
  close MyCur;

  return;
end;
$$ language 'plpgsql';

Edit:

And to use it would look like this:

Select * from MONTAR_TEXTO()
    
11.08.2016 / 21:46