Обсуждение: Concatenate

Hi,


can you help me with this tricky concat i have?
I have a function with an execute statement, one line of it doing an md5
hash of some concatenated xml paths with values. I cannot get this one
work, postgres is always complaing about some things, like: "functions
and operators can take at most one set argument"

EXECUTE'
...
md5_hash=md5(unnest(xpath(''/foo/bar'',xml_content::xml))::text) ||
(unnest(xpath(''/foo/bar2'',xml_content::xml))::text)
....


Kind regards,

Frank Jansen

Frank jansen wrote:
> can you help me with this tricky concat i have?
> I have a function with an execute statement, one line of it doing an md5
> hash of some concatenated xml paths with values. I cannot get this one
> work, postgres is always complaing about some things, like: "functions
> and operators can take at most one set argument"
>
> EXECUTE'
> ...
> md5_hash=md5(unnest(xpath(''/foo/bar'',xml_content::xml))::text) ||
> (unnest(xpath(''/foo/bar2'',xml_content::xml))::text)
> ....

That's too little, can you show more of the statement?

Yours,
Laurenz Albe

On Wed, Mar 24, 2010 at 6:22 AM, Frank jansen <jansen@fumarium.de> wrote:
> Hi,
>
>
> can you help me with this tricky concat i have?
> I have a function with an execute statement, one line of it doing an md5
> hash of some concatenated xml paths with values. I cannot get this one work,
> postgres is always complaing about some things, like: "functions and
> operators can take at most one set argument"
>
> EXECUTE'
> ...
> md5_hash=md5(unnest(xpath(''/foo/bar'',xml_content::xml))::text) ||
> (unnest(xpath(''/foo/bar2'',xml_content::xml))::text)

your problem is coming from unnest.  it takes the xml array and
returns a set.  generally speaking, functions do not operate on a
complete set, only its rows.   do you want a single digest for the
entire xpath result on each side or one for each row?

if you want a digest for the entire result, the unnest() call is not
needed. just cast the returned array to text and digest that.  if you
want to digest each row of the unnest, you need to use table
expressions and join them together.

merlin

Am 24.03.2010 14:41, schrieb Merlin Moncure:
> On Wed, Mar 24, 2010 at 6:22 AM, Frank jansen<jansen@fumarium.de>  wrote:
>
>> Hi,
>>
>>
>> can you help me with this tricky concat i have?
>> I have a function with an execute statement, one line of it doing an md5
>> hash of some concatenated xml paths with values. I cannot get this one work,
>> postgres is always complaing about some things, like: "functions and
>> operators can take at most one set argument"
>>
>> EXECUTE'
>> ...
>> md5_hash=md5(unnest(xpath(''/foo/bar'',xml_content::xml))::text) ||
>> (unnest(xpath(''/foo/bar2'',xml_content::xml))::text)
>>
> your problem is coming from unnest.  it takes the xml array and
> returns a set.  generally speaking, functions do not operate on a
> complete set, only its rows.   do you want a single digest for the
> entire xpath result on each side or one for each row?
>
> if you want a digest for the entire result, the unnest() call is not
> needed. just cast the returned array to text and digest that.  if you
> want to digest each row of the unnest, you need to use table
> expressions and join them together.
>
> merlin
>
>
Thank you for your support!
But i resolved this issue by using ARRAY_TO_STRING and array_cat

Kind regards,

Frank Jansen