argument of CASE/WHEN must not return a set

From: "George Weaver" <gweaver(at)shaw(dot)ca>
To: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
Subject: argument of CASE/WHEN must not return a set
Date: 2014-01-08 17:26:24
Message-ID: F6E2A4DE57B240D69479FA734FFF3541@D420
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Good morning,

I've have solved my problem in another way, but I am curious as to why I am getting the following error.

The following returns a boolean value a expected:

development=# SELECT LENGTH(ARRAY_TO_STRING( REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
development(# , ',')
development(# ) =
development-# LENGTH('12-70510')
development-#
development-# AS "12-70510";
12-70510
----------------
f
(1 row)

But if I put the comparison into a CASE or WHERE clause I get this error:

development=# SELECT CASE
development-# WHEN LENGTH(ARRAY_TO_STRING( REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
development(# , ',')
development(# )
development-# = LENGTH('12-70510')
development-#
development-# THEN ARRAY_TO_STRING( REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
development(# , ',')
development-#
development-# ELSE ''
development-# END AS "12-70510";
ERROR: argument of CASE/WHEN must not return a set
LINE 2: WHEN LENGTH(ARRAY_TO_STRING( REGEXP_MATCHES('12...

What am I missing?

Thanks,
George

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Nelson Green 2014-01-08 18:14:22 Re: Last inserted row id with complex PK
Previous Message Keith Fiske 2014-01-08 16:58:43 Re: Planning error in dynamic string creation in plpgsql