Postgres - How to use a LATERAL SELECT CASE to return a unique value instead of a list?-CodePudding

Considering the following table signatures referencing the signature date of a document by 2 persons

id	p1_signed_at	p2_signed_at
1	NULL	NULL
2	01/01/2022	NULL
3	NULL	07/08/2022
4	03/04/2022	04/04/2022

I want to identify the next signatory of each document.

I tried to use a FROM LATERAL to be able to filter non-null rows, it's working, but the result is a list.

How can i make postgres understand that the identity comlumn is a single value ?

SELECT 
    "id",
    "identity"

FROM 
  "signatures",
  LATERAL (
    SELECT CASE 
        WHEN "p1_signed_at" IS NULL THEN 'p1'
        WHEN "p2_signed_at" IS NULL THEN 'p2'
    END) AS "identity"

WHERE 
    "identity" IS NOT NULL

id	identity
1	(p1)
2	(p2)
3	(p1)

CodePudding user response：

"identity" is a table alias, not a column alias. If you use that in the SELECT list, it will be shown as an anonymous record. You need to give your CASE expression a proper alias to refer to the column:

SELECT 
    id,
    p."identity"
FROM 
  signatures,
  LATERAL (
    SELECT CASE 
        WHEN p1_signed_at IS NULL THEN 'p1'
        WHEN p2_signed_at IS NULL THEN 'p2'
    END) AS p("identity")
WHERE 
    p."identity" IS NOT NULL

p("identity") defines a table alias with the name p and a column with the name "identity"

The lateral cross join seems unnecessary, a simple CASE expression in the SELECT list would achieve the same.

SELECT 
    id,
    CASE 
        WHEN p1_signed_at IS NULL THEN 'p1'
        WHEN p2_signed_at IS NULL THEN 'p2'
    END as "identity"
FROM 
  signatures
WHERE p1_signed_at is null 
   OR p2_signed_at is null;

If you want to access the column alias of the CASE expression by name, you need to wrap this in a derived table:

select *
from (        
  SELECT 
      id,
      CASE 
          WHEN p1_signed_at IS NULL THEN 'p1'
          WHEN p2_signed_at IS NULL THEN 'p2'
      END as "identity"
  FROM 
    signatures
) x
where "identity" is not null