Res: converter pgplsql funcion

---

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 13:02:36
Assunto: Re: [GENERAL] converter pgplsql funcion

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