čt 20. 9. 2018 v 20:29 odesílatel Susan Hurst <
susan.hu...@brookhurstdata.com> napsal:
> Thanks, everyone!
>
> I get it now. It's not just an array but an hstore array. I changed my
> code to include the original values so now it works:
>
> -- new will be substituted for $1 during execution with using clause
>
> l_query_string := 'select ($1 #= hstore(array[' || l_orig_list || '],'
>
> || 'array[' || l_clean_list || '])).*';
>
>
>
> Pavel...I am creating a trigger function to look for columns with char,
> varchar or text data types to purge any incoming or updated data of
> extraneous spaces and tabs both within the string and on either end. We
> can use the same function from any table that calls it from a trigger. Now
> that it works, we can refactor it to make it better. I would welcome your
> suggestions for alternatives to hstore.
>
Similar task are not good for plpgsql. You can check PLPerl or PLPythonu,
that is better for these iterations over record.
It can be easy task for C extension.
> Thanks for your help!
>
with pleasure
Pavel
> Sue
>
>
>
> ---
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Susan E Hurst
> Principal Consultant
> Brookhurst Data LLC
> Email: susan.hu...@brookhurstdata.com
> Mobile: 314-486-3261
>
> On 2018-09-20 13:04, Pavel Stehule wrote:
>
> Hi
>
> čt 20. 9. 2018 v 19:55 odesílatel Susan Hurst <
> susan.hu...@brookhurstdata.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.hu...@brookhurstdata.com
>> Mobile: 314-486-3261
>>
>>
