pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (6+ messages)  latest in thread

Message-ID: <web-695893@davinci.ethosmedia.com>
Permalink:  ../web-695893@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-695893@davinci.ethosmedia.com

 · 

reply

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