String combination in SQL

Asked

Viewed 365 times

4

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

I need to develop in MySQL in PostgreSQL

To make it simpler:

TABLE DADOS_BASE

1]

How to make a process/Function that selects the records and does this:

TABELA_COMBINADA

2]

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

Considering that the DADOS_BASE TABLE has N rows and that this one has a string of n words, I am already considering that it will have a large number of rows generated in the new table.

So, what would be the best way to run this "query" that it is possible to do on a cloud server (for example scheduling execution) so that if it stops, it is resumed from the point to where it ran?

mysql postgresql

  • Your doubt is regarding the logic or structure of a Function ?

  • Hello Vitor... The logic itself.. is quiet, more or less I tried to detail the expected result. The big problem is to turn it into a Function or even a Precedent, and then schedule. I will use a mysql server inside Hotsgator to process, which I intend to do "offline", because the combinatorial analysis will be great.. I assure you that in VBA I could take lyrics, but right in BD is much more powerful.

  • And then @Mr. Burns, it worked ?

  • Victor.. Dude.. I even tried to find a contact to Skype an idea with you.. but I couldn’t find it.. I’m having some difficulties to implement.. how simple the contact could be?

  • Oops, man, as the doubt is in the stack, let’s try to settle down here to give a hand to the community. But follow my email if you have any other questions: [email protected]

1 answer

-1

Follows 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 be like this:

Select * from MONTAR_TEXTO()

Browser other questions tagged mysql postgresql

You are not signed in. Login or sign up in order to post.