Hi!
I need to make a plpgsql function and I only think on a multidimensional
array to make this function BUT I know that multidimensional arrays are
not supported in plpgsql so, I wanna to receive some ideas from this list.
Our system have a proprietary way to make his "ENCODING" , for example,
if a product description it's "Paper No. 4" it changes to "Papel
N\\012\\015 4" on the database so, changing the "o" for "\\012" and "."
for "\\015". Of course that it's not only this substitutions that it
makes, it's +/- 80 items.
As I have to access this data externaly (read) and include data directy
on the database via another application, I have to make a function that
is able to make this conversions on both ways:
DATABASE(ENCODED) => DECODE => DISPLAY
INPUT DATA => CODE => STORE ON THE DATABASE
So, my idea was:
1. Make a plpgsql function with two arguments, varchar (string to code
or decode) and a bool argument that will inform if it's to code or decode;
2. Make a multidimensional array with all the relations (code relations
Ex.: {'o', '\\015}, {'.', '\\012'}
3. According ti the bool argument make a loop and using the replace
function code or decode my string.
It's not possible insert this ENCODINGs on a table because I am not
allowed to include tables on this database, I can only use functions!
Something like this:
CREATE OR REPLACE FUNCTION arruma_memo("varchar",bool)
RETURNS "varchar" AS
$BODY$
DECLARE
old_string ALIAS FOR $1;
tipo_conversao ALIAS FOR $2;
new_string varchar;
varr_tabela varchar[];
vstr_chave varchar(50);
varr_tabela[1] :=$$'[[341','á'$$;
varr_tabela[2] :=$$'[[341','á'$$;
varr_tabela[3] :=$$'[[341','á'$$;
IF tipo_conversao IS TRUE THEN
FOR i IN 1 .. 3
LOOP
SELECT varr_tabela[i] INTO vstr_chave;
new_string := replace(old_string,vstr_chave);
END LOOP;
ELSE
FOR i IN 1 .. 3
LOOP
SELECT varr_tabela[i] INTO vstr_chave;
new_string := replace(old_string,vstr_chave);
END LOOP;
END IF;
RETURN new_string;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--
[]'s
Rodrigo Carvalhaes
--
Esta mensagem foi verificada pelo sistema de antivírus e
acredita-se estar livre de perigo.
