Re: array must have even number of elements

Hi

čt 20. 9. 2018 v 19:55 odesílatel Susan Hurst <
susan(dot)hurst(at)brookhurstdata(dot)com> napsal:

>
> Why must an array have an even number of elements? I need to use a
> trigger function on any table, some of which may have an odd number of
> columns that I want to cleanse before inserting/updating.
>

The hstore function get parameters as sequence of pairs (key, value) - so
the number should be even. Odd parameter signalize broken format.

Your example is pretty crazy - I cannot to decode it. Maybe you should to
use different function, I don't see a sense for using hstore type there.
But I cannot to decode it.

Regards

Pavel

>
> Is there a workaround for this?
>
>
> ERROR: array must have even number of elements
>
> SQL state: 2202E
>
> Context: SQL statement "SELECT ($1 #=
> hstore(array[trim(replace(regexp_replace($1.c,'( ){2,}',' ','g'),' ','
> ')),trim(replace(regexp_replace($1.vc,'( ){2,}',' ','g'),' ','
> ')),trim(replace(regexp_replace($1.t,'( ){2,}',' ','g'),' ',' '))])).*"
> PL/pgSQL function store.trim_string_before_dml() line 44 at EXECUTE
>
>
>
> -- my test table
> create table dm.trg_test (c character(8), vc varchar(16), t text);
> insert into dm.trg_test (c,vc,t) values ('ctest',' vctest ','
> ttest ');
>
>
> -- code snippet that produced the error.
> -- new will be substituted for $1 during execution with using clause
> l_query_string := 'select ($1 #= hstore(array[' || l_column_list ||
> '])).*';
> execute format(l_query_string) using new into new;
> return new;
>
>
> Thanks for your help!
>
> --
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Susan E Hurst
> Principal Consultant
> Brookhurst Data LLC
> Email: susan(dot)hurst(at)brookhurstdata(dot)com
> Mobile: 314-486-3261
>
>