2008/11/21 paulo matadr <saddoness@xxxxxxxxxxxx>:> 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, thatis 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
regardsPavel 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@xxxxxxxxx>> Para: paulo matadr <saddoness@xxxxxxxxxxxx>> Cc: GENERAL <pgsql-general@xxxxxxxxxxxxxx>> Enviadas: Sexta-feira, 21 de Novembro de 2008 10:05:02> Assunto: Re: 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@xxxxxxxxxxxx>:>> 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
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
