Res: converter pgplsql funcion

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('[');

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;

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(dot)stehule(at)gmail(dot)com>
Para: paulo matadr <saddoness(at)yahoo(dot)com(dot)br>
Cc: GENERAL <pgsql-general(at)postgresql(dot)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(at)yahoo(dot)com(dot)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
http://br.maisbuscados.yahoo.com