SQL string combination

Posted on

Question :

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:




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>

Answer :

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 $$
  Dcl_TextoID numeric(18,0);
  Dcl_TextoText varchar(500);

  MyCur refcursor;

  result RET_TEXTO;

  open MyCur for select ID, tx_texto from tb_texto ;
    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;

$$ language 'plpgsql';


And to use it would look like this:

Select * from MONTAR_TEXTO()


Leave a Reply

Your email address will not be published. Required fields are marked *