Re: new psql \d command - Mailing list pgsql-hackers

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
withtime 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
withtime 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_idIS NOT NULL AND
 
palm_buyers.affiliate_amount ISNOT NULL
UNION ALLSELECT 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 ISNOT NULL AND palm_buyers.refund_datetime IS NOT NULL
UNION ALLSELECT 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 ISNOT NULL
UNION ALLSELECT 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 ISNOT NULL AND shop_orders.refund_datetime IS NOT NULL
UNION ALLSELECT 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 ALLSELECT 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 ALLSELECT 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@sss.pgh.pa.us>
To: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Cc: "pgsql-hackers list" <pgsql-hackers@postgresql.org>
Sent: Thursday, August 07, 2003 11:17 PM
Subject: Re: new psql \d command


> Christopher Kings-Lynne <chriskl@familyhealth.com.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
>