Re: More problems with 7.2 & functions - Mailing list pgsql-sql

Folks, Tom:

More on that error:

The error:


Is being caused by a view referenced in the function.  As I saidbefore, this view worked under Postgres 7.1.3 but now
itfails under7.2.  What follows is the view definition.  Sorry it's not veryformatted, but I can't find my source file
copyso 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
statusUSING (status)) JOINtext_lists ON ((invoices.invoice_type = text_lists.list_id))) JOINorders 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 ((((invoicesJOIN status USING (status)) JOIN
text_listsON ((invoices.invoice_type= text_lists.list_id))) JOIN clients ON ((invoices.ref_usq =clients.usq))) JOIN
clientscl2 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_usqFROM (((((invoices JOINstatus 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 ~* '^pertemp'::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 JOINvw_bills_pending vbp ON
((clients.usq= vbp.client_usq)));
 

Grazie!

-Josh


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco