From: Josh Berkus <josh@agliodbs.com>
To: Josh Berkus <josh@agliodbs.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-sql@postgresql.org
Subject: Re: More problems with 7.2 & functions
Date: Sat, 16 Feb 2002 10:52:31 -0800
Message-ID: <web-695893@davinci.ethosmedia.com> (raw)
In-Reply-To: <web-695879@davinci.ethosmedia.com>
Folks, Tom:
More on that error:
The error:
Is being caused by a view referenced in the function. As I said
before, this view worked under Postgres 7.1.3 but now it fails under
7.2. What follows is the view definition. Sorry it's not very
formatted, but I can't find my source file copy so it's from pg_dump.
CREATE VIEW "sv_invoices" as
SELECT invoices.usq, invoices.end_date, invoices.status,
status.status_label, invoices.total_amount, invoices.invoice_no,
clients.client_name, orders.position_type AS invoice_name,
invoices.client_usq FROM ((((invoices JOIN status USING (status)) JOIN
text_lists ON ((invoices.invoice_type = text_lists.list_id))) JOIN
orders ON ((invoices.ref_usq = orders.usq))) JOIN clients ON
((invoices.client_usq = clients.usq))) WHERE (((status.relation =
'invoices'::"varchar") AND (invoices.status <> 0)) AND
(text_lists.list_value ~* '^per order'::text
UNION SELECT invoices.usq, invoices.end_date, invoices.status,
status.status_label, invoices.total_amount, invoices.invoice_no,
cl2.client_name, 'Combined', invoices.client_usq FROM ((((invoices
JOIN status USING (status)) JOIN text_lists ON ((invoices.invoice_type
= text_lists.list_id))) JOIN clients ON ((invoices.ref_usq =
clients.usq))) JOIN clients cl2 ON ((invoices.client_usq = cl2.usq)))
WHERE (((status.relation = 'invoices'::"varchar") AND (invoices.status
<> 0)) AND (text_lists.list_value ~* '^combined'::text)))
UNION SELECT invoices.usq, invoices.end_date, invoices.status,
status.status_label, invoices.total_amount, invoices.invoice_no,
clients.client_name, ((candidates.last_name || ', '::"varchar") ||
candidates.first_name), invoices.client_usq FROM (((((invoices JOIN
status USING (status)) JOIN text_lists ON ((invoices.invoice_type =
text_lists.list_id))) JOIN assignments ON ((invoices.ref_usq =
assignments.usq))) JOIN candidates ON ((assignments.candidate_usq =
candidates.usq))) JOIN clients ON ((invoices.client_usq =
clients.usq))) WHERE (((status.relation = 'invoices'::"varchar") AND
(invoices.status <> 0)) AND (text_lists.list_value ~* '^per
temp'::text)))
UNION SELECT (clients.usq * -1),
qf_next_invoice_due(clients.invoice_interval) AS qf_next_invoice_due,
-255, 'Next Due', vbp.total_timecards, NULL::VARCHAR,
clients.client_name, '', clients.usq FROM (clients JOIN
vw_bills_pending vbp ON ((clients.usq = vbp.client_usq)));
Grazie!
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, tgl@sss.pgh.pa.us
Subject: Re: More problems with 7.2 & functions
In-Reply-To: <web-695893@davinci.ethosmedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox