En utilisant les colonnes sélectionnées pour créer une autre interpolation de chaîne combinée de tous les champs dans Postgresql?

Question

J'ai cette grande requête Postgresql (je poste seulement le Select pour des raisons de concision):

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,

J'aimerais ajouter une autre colonne appelée Aperçu qui combine les champs papa_pal, scheduled_for et hours que j'ai sélectionnés là-bas en une seule chaîne:

Aperçu: "Nom: ${papa_pal}\nDate: ${scheduled_for\nHeures: ${hours}"

Des recommandations sur la façon d'accomplir cela?

En essayant simplement d'utiliser les champs, j'obtiens cette erreur:

Requête:

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('Aperçu: "Nom: %s\nDate: %s\nHeures: %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

Erreur:

 Query 1 ERROR: ERROR:  colonne "papa_pal" n'existe pas
LINE 24: ...rmat('Aperçu: "Nom: %s\nDate: %s\nHeures: %s"', papa_pal, ...
                                                              ^
HINT:  Peut-être vouliez-vous faire référence à la colonne "v.papa_id" ou à la colonne "visit_location.papa_id".

Answer 1

Vous devriez pouvoir utiliser la fonction format():

SELECT
v.id AS visit_id,
...
TRUNC((EXTRACT(EPOCH FROM (v.completed_at - v.started_at)) / 60 / 60)::numeric, 2) AS hours,
format('Aperçu : "Nom: %s\nDate: %s\nHeures: %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 ...

Vous voudrez peut-être aussi convertir TRUNC((EXTRACT(EPOCH FROM (v.completed_at - v.started_at)) / 60 / 60)::numeric, 2) en date_part('hour',v.completed_at - v.started_at)