Обсуждение: converter pgplsql funcion

Hello

create or replace function clean_string(_p_dado varchar)
returns varchar as $$
declare
  _v_clean_string varchar := _p_dado;
  _c varchar;
begin
  for _c in select caracter from caracters loop
    _v_clean_string := replace(_c_clean_string, _c);
  end loop;
  return _c;
end;
$$ language plpgsql immutable strict;

regards
Pavel Stehule

p.s. look on translate function, maybe it should help

http://www.postgresql.org/docs/8.3/interactive/functions-string.html

regards
Pavel Stehule

2008/11/21 paulo matadr <saddoness@yahoo.com.br>:
> I work with oracle and have poor experience in pg/plsql.
> anybody can  help me  with translate from   pl/sql in pg/plsql in code
> below:
>
> CREATE OR REPLACE FUNCTION clean_string(p_dado varchar2) RETURN varchar2
> IS
>   v_clean_string varchar(4000);
> BEGIN
>     v_clean_string := p_dado;
>      for r in (select caracter from caracteres) loop
>           select replace(v_clean_string,r.caracter,'')
>          into v_clean_string
>          from dual;
>     end loop;
>     return v_clean_string;
> END;
> /
>
> need funcion in pg/plsql
> thanks for help
>
>
> ________________________________
> Veja quais são os assuntos do momento no Yahoo! + Buscados: Top 10 -
> Celebridades - Música - Esportes

2008/11/21 paulo matadr <saddoness@yahoo.com.br>:
> not working.. i try to explain my dought
> my idea is:
> 1- create a table for storage special caracters,
> create table caracteres(
> caracter character varying
> );
> insert into caracteres values(':');
> insert into caracteres values(';');
> insert into caracteres values('<');
> insert into caracteres values('=');
> insert into caracteres values('>');
> insert into caracteres values('?');
> insert into caracteres values('[');

Your idea isn't good. You replace integrated function translate, that
is much faster.

>
> after
> 2- create a funcion:
> create or replace function clean_string(_p_dado character varying)
> returns character varying as $$
> declare
>   _v_clean_string character varying := _p_dado;
>   _c character varying;
> begin
>   for _c in select caracter from caracters loop
>     _v_clean_string := btrim(_c_clean_string, _c);
>   end loop;
>   return _c;
> end;
> $$ language plpgsql immutable strict;
>
> for make a command
> update TABLE set COLUMN = clear_string(COLUMN);
> ------------------------------------------------------------
> atually a had follow error:
> create table caracteres(
> caracter character varying
> );
>
>
> Consulta executada com sucesso sem resultados em 78 ms.
>
> -- Executando consulta:
> insert into caracteres values('"');
>
>
> Consulta executada com sucesso: 1 registros afetados, com tempo de execução
> de 0 ms.
>
> -- Executando consulta:
> create or replace function clean_string(_p_dado character varying)
> returns character varying as $$
> declare
>   _v_clean_string character varying := _p_dado;
>   _c character varying;
> begin
>   for _c in select caracter from caracters loop
>     _v_clean_string := btrim(_c_clean_string, _c);
>   end loop;
>   return _c;
> end;
> $$ language plpgsql immutable strict;
>
>

is there any bug? What is result of CREATE FUNCTION statement

regards
Pavel Stehule
>
> Consulta executada com sucesso sem resultados em 16 ms.
>
> -- Executando consulta:
> update cadastro.cliente set clie_nmcliente = clear_string(clie_nmcliente);
>
>
> ERROR:  function clear_string(character varying) does not exist
> LINE 1: update cadastro.cliente set clie_nmcliente = clear_string(cl...
>                                                      ^
> HINT:  No function matches the given name and argument types. You may need
> to add explicit type casts.
>
> ********** Erro **********
>
> ERROR: function clear_string(character varying) does not exist
> SQL state: 42883
> Dica: No function matches the given name and argument types. You may need to
> add explicit type casts.
> Caracter: 46
>
> any idea for help ou give another way to complete this ?
>
>
>
> ________________________________
> De: Pavel Stehule <pavel.stehule@gmail.com>
> Para: paulo matadr <saddoness@yahoo.com.br>
> Cc: GENERAL <pgsql-general@postgresql.org>
> Enviadas: Sexta-feira, 21 de Novembro de 2008 10:05:02
> Assunto: Re: [GENERAL] converter pgplsql funcion
>
> Hello
>
> create or replace function clean_string(_p_dado varchar)
> returns varchar as $$
> declare
>   _v_clean_string varchar := _p_dado;
>   _c varchar;
> begin
>   for _c in select caracter from caracters loop
>     _v_clean_string := replace(_c_clean_string, _c);
>   end loop;
>   return _c;
> end;
> $$ language plpgsql immutable strict;
>
> regards
> Pavel Stehule
>
> p.s. look on translate function, maybe it should help
>
> http://www.postgresql.org/docs/8.3/interactive/functions-string.html
>
> regards
> Pavel Stehule
>
> 2008/11/21 paulo matadr <saddoness@yahoo.com.br>:
>> I work with oracle and have poor experience in pg/plsql.
>> anybody can  help me  with translate from  pl/sql in pg/plsql in code
>> below:
>>
>> CREATE OR REPLACE FUNCTION clean_string(p_dado varchar2) RETURN varchar2
>> IS
>>  v_clean_string varchar(4000);
>> BEGIN
>>    v_clean_string := p_dado;
>>      for r in (select caracter from caracteres) loop
>>          select replace(v_clean_string,r.caracter,'')
>>          into v_clean_string
>>          from dual;
>>    end loop;
>>    return v_clean_string;
>> END;
>> /
>>
>> need funcion in pg/plsql
>> thanks for help
>>
>>
>> ________________________________
>> Veja quais são os assuntos do momento no Yahoo! + Buscados: Top 10 -
>> Celebridades - Música - Esportes
>
> ________________________________
> Veja quais são os assuntos do momento no Yahoo! + Buscados: Top 10 -
> Celebridades - Música - Esportes

paulo matadr wrote:
>
> -- Executando consulta:
> create or replace function clean_string(_p_dado character varying)
                                ^^^
clea[n]_string

> ********** Erro **********
>
> ERROR: function clear_string(character varying) does not exist
                     ^^^
clea[r]_string

> SQL state: 42883
> Dica: No function matches the given name and argument types. You may need to add explicit type casts.
> Caracter: 46
>
> any idea for help ou give another way to complete this ?

Bus see what Pavel said about this being a slow way of doing it.

--
  Richard Huxton
  Archonet Ltd

On Fri, Nov 21, 2008 at 7:37 AM, paulo matadr <saddoness@yahoo.com.br> wrote:
> I work with oracle and have poor experience in pg/plsql.
> anybody can  help me  with translate from   pl/sql in pg/plsql in code
> below:

See OraToPg:

You can download it here: http://pgfoundry.org/projects/ora2pg/
You can read about it and using it here:

http://64.233.169.132/search?q=cache:ko5k7eHQvrgJ:kb.cospa-project.org/retrieve/4051/chapter6.pdf+%22Bridging+Tool:+OraToPG%22&hl=en&ct=clnk&cd=1&gl=us

--
Jonah H. Harris, Senior DBA
myYearbook.com