Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1GIqXE96309 for ; Sat, 16 Feb 2002 13:52:33 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.2) with HTTP id 695893; Sat, 16 Feb 2002 10:52:31 -0800 From: "Josh Berkus" Subject: Re: More problems with 7.2 & functions To: "Josh Berkus" , Tom Lane , pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.5.2 Date: Sat, 16 Feb 2002 10:52:31 -0800 Message-ID: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200202/199 X-Sequence-Number: 6625 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