Re: array must have even number of elements
č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
>>
>>
Re: array must have even number of elements
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.
Thanks for your help!
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
> 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 [1],'( ){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
Links:
--
[1] http://1.vc
Re: array must have even number of elements
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
>
>
array must have even number of elements
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.
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
