Run Postgres string function on column created in same query

Using Postgres 11, how can I execute a series of string functions in the same query, where subsequent string function calls execute against the results of previously executed string functions?

My source table labels looks like this:

descr
12345_orange
23456B_yellow
34567C_blue

I would like to create a view that can return this:

descr	id	rate	name
12345_orange	12345		orange
23456B_yellow	23456	B	yellow
34567C_blue	34567	C	blue

A tricky part here is that the column desc is not consistent. Sometimes the value rate exists and sometimes it does not, as shown in the first row. It seems the easiest way to solve this would be to use a series of two functions where first I extract the value for id and store that in a column. Then from that column extract the value for rate, if it exists.

Here is the query I am attempting:

SELECT
    labels.descr,
    SPLIT_PART(labels.descr, '_', 1) as id
    , REGEXP_MATCHES(id, '[A-Za-z]')
FROM
    labels
;

When I execute that I receive the error that column id does not exist.

Can someone help me understand how I can solve this problem? I am hoping to solve this using a view only, for simplicity in future queries against this result set.

Additionally I would really appreciate an insight into why the above does not work. Thank you!

Solution

You have to use subqueries:

SELECT labels_descr,
       id,
       REGEXP_MATCHES(id, '[A-Za-z]')
FROM (SELECT labels.descr,
             SPLIT_PART(labels.descr, '_', 1) as id
      FROM labels) AS subq;

or CTEs:

WITH subq AS (
   SELECT labels.descr,
          SPLIT_PART(labels.descr, '_', 1) as id
   FROM labels)
SELECT labels_descr,
       id,
       REGEXP_MATCHES(id, '[A-Za-z]')
FROM subq;