[SQL] Adding a column to a VIEW which has dependent objects.

[Rajesh Kumar Mallah]

Dear PostgreSQL gurus,

How do people extend a parent view which has
lot of dependent views?
The parent view cannot be dropped because that will
require recreating a dozen of dependent views.
Is there any workaround.

Also is there an easy way of dumping the definitions
of all the dependent views of a given object. Does information_schema
helps here.
Regds
mallah.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Adding a column to a VIEW which has dependent objects.

[Tom Lane]

Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes:
> How do people extend a parent view which has
> lot of dependent views?
> The parent view cannot be dropped because that will
> require recreating a dozen of dependent views.

You're out of luck, you'll have to drop and remake them all.
In future we could think about some kind of ALTER VIEW ADD COLUMN
operation, but it ain't there now.

(I suppose if you were really desperate you could think about manually
hacking the system catalogs, but this would be pretty risky on a
production database.)

> Also is there an easy way of dumping the definitions
> of all the dependent views of a given object.

You can chase the links in pg_depend to see what the dependent objects
are, but extracting their definitions would be a tad harder ...

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] data loading

[Jeff Eckermann]

--- Richard Huxton <[EMAIL PROTECTED]> wrote:
> On Friday 09 January 2004 02:13, [EMAIL PROTECTED]
> wrote:
> > Hi,
> >
> >
> >   i try to load data from flat file (comma
> delimiter format) into
> > temporary table . i use COPY command as below:
> >
> > dwnc=# copy biosadm.custdo_temp
> > dwnc-# from
> '/home/bios/customer_data/CustomerDO_new.CSV'
> > dwnc-# WITH DELIMITER ',' ;
> >
> > ERROR:  copy: line 141, Extra data after last
> expected column
> 
> Does line 141 (or nearby) have a comma somewhere in
> its data? That would fool 
> the COPY into mis-counting the columns. See the COPY
> entry in the SQL Command 
> Reference chapter of the manuals - you'll want to
> escape any commas with a 
> backslash:
>  \,

CSV deals with embedded commas by quoting the data
field that contains it.  Applications that are aware
of this will know to ignore commas within quoted
strings.  COPY is not smart enough to figure this out
(nor smart enough to strip off any quotes, which will
be imported as data).  If you know for a fact that
your data contains no quotes, then you can get away
with the "with delimiters ','" trick.  Otherwise, you
will need to preprocess your data through something
that will parse the CSV format, e.g. some Perl module.
 Note that hand rolled solutions usually underestimate
the complexity of dealing with CSV data, and why
bother when others have already solved the problem for you.

__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match