Re: converter pgplsql funcion
От | Pavel Stehule |
---|---|
Тема | Re: converter pgplsql funcion |
Дата | |
Msg-id | 162867790811210802m67b4768es2daf3a1e74e8df82@mail.gmail.com обсуждение исходный текст |
Ответ на | Res: converter pgplsql funcion (paulo matadr <saddoness@yahoo.com.br>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления:
Предыдущее
От: "Nikolas Everett"Дата:
Сообщение: Re: Using Postgres to store high volume streams of sensor readings
Следующее
От: Tom LaneДата:
Сообщение: Re: Using Postgres to store high volume streams of sensor readings