Re: new psql \d command

Well for me it's the difference between this:

australia=# \d affiliates_transactions
View "public.affiliates_transactions"
Column | Type | Modifiers
--------------+--------------------------+-----------
affiliate_id | integer |
date | timestamp with time zone |
type | text |
type_id | integer |
amount | numeric |
View definition: ((((((((((((SELECT palm_buyers.affiliate_id,
timestamptz(abstime(palm_buyers.datetime)) AS date, 'Palm' AS "type", 1 AS
type_id, palm_buyers.affiliate_amount AS amount FROM palm_buyers WHERE
((palm_buyers.affiliate_id IS NOT NULL) AND (palm_buyers.affiliate_amount IS
NOT NULL))) UNION ALL (SELECT palm_buyers.affiliate_id,
timestamptz(abstime(palm_buyers.refund_datetime)) AS date, 'Palm Refund' AS
"type", 2 AS type_id, (- palm_buyers.affiliate_amount) AS amount FROM
palm_buyers WHERE (((palm_buyers.affiliate_id IS NOT NULL) AND
(palm_buyers.affiliate_amount IS NOT NULL)) AND (palm_buyers.refund_datetime
IS NOT NULL))))) UNION ALL (SELECT shop_orders.affiliate_id,
timestamptz((shop_orders.datetime)::abstime) AS date, 'Books' AS "type", 3
AS type_id, shop_orders.affiliate_amount AS amount FROM shop_orders WHERE
((shop_orders.affiliate_id IS NOT NULL) AND (shop_orders.affiliate_amount IS
NOT NULL))))) UNION ALL (SELECT shop_orders.affiliate_id,
timestamptz(abstime(shop_orders.refund_datetime)) AS date, 'Books Refund' AS
"type", 4 AS type_id, (- shop_orders.affiliate_amount) AS amount FROM
shop_orders WHERE (((shop_orders.affiliate_id IS NOT NULL) AND
(shop_orders.affiliate_amount IS NOT NULL)) AND (shop_orders.refund_datetime
IS NOT NULL))))) UNION ALL (SELECT transactions_log.affiliate_id,
timestamptz(transactions_log.date) AS date, 'Site' AS "type", 5 AS type_id,
transactions_log.affiliate_amount AS amount FROM transactions_log WHERE
((transactions_log.affiliate_id IS NOT NULL) AND
(transactions_log.affiliate_amount IS NOT NULL))))) UNION ALL (SELECT
transactions_log.affiliate_id,
timestamptz(abstime(transactions_log.refund_datetime)) AS date, 'Site
Refund' AS "type", 6 AS type_id, (- transactions_log.affiliate_amount) AS
amount FROM transactions_log WHERE (((transactions_log.affiliate_id IS NOT
NULL) AND (transactions_log.affiliate_amount IS NOT NULL)) AND
(transactions_log.refund_datetime IS NOT NULL))))) UNION ALL (SELECT
affiliates_payments.affiliate_id, affiliates_payments.datetime AS date,
'Affiliate Payment' AS "type", 7 AS type_id, (- affiliates_payments.amount)
AS amount FROM affiliates_payments));

and this:

australia=# \d affiliates_transactions
View "public.affiliates_transactions"
Column | Type | Modifiers
--------------+--------------------------+-----------
affiliate_id | integer |
date | timestamp with time zone |
type | text |
type_id | integer |
amount | numeric |
View definition:
SELECT palm_buyers.affiliate_id, timestamptz(abstime(palm_buyers.datetime))
AS
date, 'Palm' AS "type", 1 AS type_id, palm_buyers.affiliate_amount AS amount
FROM palm_buyers
WHERE palm_buyers.affiliate_id IS NOT NULL AND
palm_buyers.affiliate_amount IS
NOT NULL
UNION ALL
SELECT palm_buyers.affiliate_id,
timestamptz(abstime(palm_buyers.refund_datetim
e)) AS date, 'Palm Refund' AS "type", 2 AS type_id, -
palm_buyers.affiliate_amou
nt AS amount
FROM palm_buyers
WHERE palm_buyers.affiliate_id IS NOT NULL AND
palm_buyers.affiliate_amount IS
NOT NULL AND palm_buyers.refund_datetime IS NOT NULL
UNION ALL
SELECT shop_orders.affiliate_id, timestamptz(shop_orders.datetime::abstime)
AS
date, 'Books' AS "type", 3 AS type_id, shop_orders.affiliate_amount AS
amount
FROM shop_orders
WHERE shop_orders.affiliate_id IS NOT NULL AND
shop_orders.affiliate_amount IS
NOT NULL
UNION ALL
SELECT shop_orders.affiliate_id,
timestamptz(abstime(shop_orders.refund_datetim
e)) AS date, 'Books Refund' AS "type", 4 AS type_id, -
shop_orders.affiliate_amo
unt AS amount
FROM shop_orders
WHERE shop_orders.affiliate_id IS NOT NULL AND
shop_orders.affiliate_amount IS
NOT NULL AND shop_orders.refund_datetime IS NOT NULL
UNION ALL
SELECT transactions_log.affiliate_id, timestamptz(transactions_log.date) AS
dat
e, 'Site' AS "type", 5 AS type_id, transactions_log.affiliate_amount AS
amount
FROM transactions_log
WHERE transactions_log.affiliate_id IS NOT NULL AND
transactions_log.affiliate
_amount IS NOT NULL
UNION ALL
SELECT transactions_log.affiliate_id,
timestamptz(abstime(transactions_log.refu
nd_datetime)) AS date, 'Site Refund' AS "type", 6 AS type_id, -
transactions_log
.affiliate_amount AS amount
FROM transactions_log
WHERE transactions_log.affiliate_id IS NOT NULL AND
transactions_log.affiliate
_amount IS NOT NULL AND transactions_log.refund_datetime IS NOT NULL
UNION ALL
SELECT affiliates_payments.affiliate_id, affiliates_payments.datetime AS
date,
'Affiliate Payment' AS "type", 7 AS type_id, - affiliates_payments.amount AS
amo
unt
FROM affiliates_payments;

The second is a lot more readable :)

Chris

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au>
Cc: "pgsql-hackers list" <pgsql-hackers(at)postgresql(dot)org>
Sent: Thursday, August 07, 2003 11:17 PM
Subject: Re: new psql \d command

> Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> writes:
> > It might be a bit risky getting pg_dump to use it though?
>
> I definitely don't want pg_dump using the pretty-print stuff ;-).
> I'm neutral on whether to use it in psql's \d commands.
>
> regards, tom lane
>