CODEWITHSUNDEEP
sqlpostgresqlselect
Some dood
Some dood

Reputation: 145

Using selected columns to create another string interpolation combined of all fields in Postgresql?

I have this large Postgresql query (posting only the Select for brevity):

SELECT
v.id AS visit_id,
a. "type" AS account_type,
a.full_name AS member,
papa.full_name AS papa,
pal_account.full_name AS papa_pal,
pal_account.email AS pal_email,
visit_location. "state" AS visit_location,
to_char(v.scheduled_for::timestamp AT time zone 'UT', 'YYYY-MM-DD HH:MI AM') AS scheduled_for,
to_char(v.started_at::timestamp AT time zone 'UT', 'YYYY-MM-DD HH:MI AM') AS start_time,
to_char(v.completed_at::timestamp AT time zone 'UT', 'YYYY-MM-DD HH:MI AM') AS end_time,
TRUNC((EXTRACT(EPOCH FROM (v.completed_at - v.started_at)) / 60)::numeric, 2) AS minutes,
TRUNC((EXTRACT(EPOCH FROM (v.completed_at - v.started_at)) / 60 / 60)::numeric, 2) AS hours,

I would like to add another column called Overview that combines the pal_pal, scheduled_for and hours fields I selected there into a single string:

Overview: "Name: ${papa_pal}\nDate: ${scheduled_for\nHours: ${hours}"

Any recommendations on how to accomplish this?

Trying to just use the fields, I get this error:

Query:

SELECT
    v.id AS visit_id,   
    a. "type" AS account_type,
    a.full_name AS member,
    papa.full_name AS papa,
    pal_account.full_name AS papa_pal,
    pal_account.email AS pal_email,
    visit_location. "state" AS visit_location,
    to_char(v.scheduled_for::timestamp AT time zone 'UT', 'YYYY-MM-DD HH:MI AM') AS scheduled_for,
    to_char(v.started_at::timestamp AT time zone 'UT', 'YYYY-MM-DD HH:MI AM') AS start_time,
    to_char(v.completed_at::timestamp AT time zone 'UT', 'YYYY-MM-DD HH:MI AM') AS end_time,
    TRUNC((EXTRACT(EPOCH FROM (v.completed_at - v.started_at)) / 60)::numeric, 2) AS minutes,
    TRUNC((EXTRACT(EPOCH FROM (v.completed_at - v.started_at)) / 60 / 60)::numeric, 2) AS hours,
    CASE WHEN v.visit_distance IS NOT NULL THEN
        v.visit_distance / 1609.34
    ELSE
        sum(paths.visit_distance) / 1609.34
    END AS total_visit_distance,
    CASE WHEN v.commute_distance IS NOT NULL THEN
        v.commute_distance / 1609.34
    ELSE
        sum(paths.commute_distance) / 1609.34
    END AS total_commute_distance,
    format('Overview: "Name: %s\nDate: %s\nHours: %s"', papa_pal, scheduled_for, hours) as overview,
    (
        SELECT
            (plans.data::json -> 'metadata' ->> 'hourly_rate')::INTEGER / 100
        FROM
            plans
        WHERE
            plans.data -> 'id' = a.stripe_plan -> 'id') AS member_rate

Error:

 Query 1 ERROR: ERROR:  column "papa_pal" does not exist
LINE 24: ...rmat('Overview: "Name: %s\nDate: %s\nHours: %s"', papa_pal, ...
                                                              ^
HINT:  Perhaps you meant to reference the column "v.papa_id" or the column "visit_location.papa_id".

Upvotes: 9

Views: 5763

Answers (1)

richyen
richyen

Reputation: 10018

You should be able to use the format() function:

SELECT
v.id AS visit_id,
...
TRUNC((EXTRACT(EPOCH FROM (v.completed_at - v.started_at)) / 60 / 60)::numeric, 2) AS hours,
format('Overview: "Name: %s\nDate: %s\nHours: %s"', pal_account.full_name, to_char(v.scheduled_for::timestamp AT time zone 'UT', 'YYYY-MM-DD HH:MI AM'), TRUNC((EXTRACT(EPOCH FROM (v.completed_at - v.started_at)) / 60 / 60)::numeric, 2)) as overview
FROM ...
WHERE ...

You may also want to convert TRUNC((EXTRACT(EPOCH FROM (v.completed_at - v.started_at)) / 60 / 60)::numeric, 2) into date_part('hour',v.completed_at - v.started_at)

Upvotes: 7

Related Questions