Thread: Export a column in a view without "announcing" it?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I'm fairly certain there is currently no way to do this, but it would
make life a bit easier for me right now if there were, so I guess I'm
going to ask it anyway, just in case:
Is there any way to expose a column in a view without "announcing" the
fact that it is there? Specifically, in order to support updatable
cursors on views (faked ones, using the ODBC driver), it is necessary
to include the underlying table's OID and CTID columns in the view; but
the client software I am trying to use then chokes on there being a
column named OID (which is present, but hidden, if I use a table). So
basically I would like to do:
CREATE VIEW aView AS
SELECT x, y, OID, CTID, z, t, ... FROM aTable WHERE condition_met
WITHOUT ANNOUNCING OID, CTID
or some such, so that if a query requesting OID or CTID is made, it
will be there, while if a client asks for a list of columns, OID and
CTID are not given?
Also: with respect to the "fake" updatable cursors: if the client
program attempts an INSERT operation and does not provide values for
OID and CTID, am I correct in assuming that the database will correctly
fill in these columns automatically?
Thank you!
- -----------------------------------------------------------
Frank D. Engel, Jr. <fde101@fjrhome.net>
$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)
iD8DBQFB98Dg7aqtWrR9cZoRAhQZAJ9YcDwir3mvcPESGEuduVHWIeXfzwCeMO/+
iQ7q3x86VNjjpoywOXr4KJ4=
=uROx
-----END PGP SIGNATURE-----
___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com
I don't know how this works for a view, but in the ODBC driver settings there is an option on page 2 to show the OID column. I am using linked tables in Access and it does not show the OID column because the checkmark is not clicked. I would suspect it works the same for views. In any case, in order to support updates on views you need to define a non-conditional update rule for the view, so that it actually updates the underlying table. I did it for the first time yesterday and found the concept quite interesting. Sim ""Frank D. Engel, Jr."" <fde101@fjrhome.net> wrote in message news:BF620526-6FB4-11D9-B323-0050E410655F@fjrhome.net... > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > I'm fairly certain there is currently no way to do this, but it would > make life a bit easier for me right now if there were, so I guess I'm > going to ask it anyway, just in case: > > Is there any way to expose a column in a view without "announcing" the > fact that it is there? Specifically, in order to support updatable > cursors on views (faked ones, using the ODBC driver), it is necessary > to include the underlying table's OID and CTID columns in the view; but > the client software I am trying to use then chokes on there being a > column named OID (which is present, but hidden, if I use a table). So > basically I would like to do: > > CREATE VIEW aView AS > SELECT x, y, OID, CTID, z, t, ... FROM aTable WHERE condition_met > WITHOUT ANNOUNCING OID, CTID > > > or some such, so that if a query requesting OID or CTID is made, it > will be there, while if a client asks for a list of columns, OID and > CTID are not given? > > Also: with respect to the "fake" updatable cursors: if the client > program attempts an INSERT operation and does not provide values for > OID and CTID, am I correct in assuming that the database will correctly > fill in these columns automatically? > > Thank you! > > - ----------------------------------------------------------- > Frank D. Engel, Jr. <fde101@fjrhome.net> > > $ ln -s /usr/share/kjvbible /usr/manual > $ true | cat /usr/manual | grep "John 3:16" > John 3:16 For God so loved the world, that he gave his only begotten > Son, that whosoever believeth in him should not perish, but have > everlasting life. > $ > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.2.4 (Darwin) > > iD8DBQFB98Dg7aqtWrR9cZoRAhQZAJ9YcDwir3mvcPESGEuduVHWIeXfzwCeMO/+ > iQ7q3x86VNjjpoywOXr4KJ4= > =uROx > -----END PGP SIGNATURE----- > > > > ___________________________________________________________ > $0 Web Hosting with up to 120MB web space, 1000 MB Transfer > 10 Personalized POP and Web E-mail Accounts, and much more. > Signup at www.doteasy.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
On Wed, 26 Jan 2005 11:10:07 -0500, Frank D. Engel, Jr.
<fde101@fjrhome.net> wrote:
> I'm fairly certain there is currently no way to do this, but it would
So am I. :)
> Is there any way to expose a column in a view without "announcing" the
> fact that it is there? Specifically, in order to support updatable
> cursors on views (faked ones, using the ODBC driver), it is necessary
> to include the underlying table's OID and CTID columns in the view; but
> the client software I am trying to use then chokes on there being a
> column named OID (which is present, but hidden, if I use a table). So
> basically I would like to do:
>
> CREATE VIEW aView AS
> SELECT x, y, OID, CTID, z, t, ... FROM aTable WHERE condition_met
> WITHOUT ANNOUNCING OID, CTID
Oh, I think it would be quite useful in non-ODBC world,
suppose I have:
CREATE VIEW av AS
SELECT foo_id, foo_name, foo_bar, foo_baz FROM blah
WITHOUT ANNOUNCING foo_id;
...means that I can later do things like:
SELECT foo_name FROM av ORDER BY foo_id;
...but also (much more important) use this foo_id in joins!
This could be especially useful when doing view-on-a-view. :)
Regards,
Dawid
PS: Though I'm somewhat sceptical towards "WITHOUT ANNOUNCING" syntax.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 That option was already turned off, and I already have an unconditional update rule on the view. On Jan 27, 2005, at 1:51 AM, Sim Zacks wrote: > I don't know how this works for a view, but in the ODBC driver settings > there is an option on page 2 to show the OID column. I am using linked > tables in Access and it does not show the OID column because the > checkmark > is not clicked. I would suspect it works the same for views. > > In any case, in order to support updates on views you need to define a > non-conditional update rule for the view, so that it actually updates > the > underlying table. I did it for the first time yesterday and found the > concept quite interesting. > > Sim > > ""Frank D. Engel, Jr."" <fde101@fjrhome.net> wrote in message > news:BF620526-6FB4-11D9-B323-0050E410655F@fjrhome.net... >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> I'm fairly certain there is currently no way to do this, but it would >> make life a bit easier for me right now if there were, so I guess I'm >> going to ask it anyway, just in case: >> >> Is there any way to expose a column in a view without "announcing" the >> fact that it is there? Specifically, in order to support updatable >> cursors on views (faked ones, using the ODBC driver), it is necessary >> to include the underlying table's OID and CTID columns in the view; >> but >> the client software I am trying to use then chokes on there being a >> column named OID (which is present, but hidden, if I use a table). So >> basically I would like to do: >> >> CREATE VIEW aView AS >> SELECT x, y, OID, CTID, z, t, ... FROM aTable WHERE condition_met >> WITHOUT ANNOUNCING OID, CTID >> >> >> or some such, so that if a query requesting OID or CTID is made, it >> will be there, while if a client asks for a list of columns, OID and >> CTID are not given? >> >> Also: with respect to the "fake" updatable cursors: if the client >> program attempts an INSERT operation and does not provide values for >> OID and CTID, am I correct in assuming that the database will >> correctly >> fill in these columns automatically? >> >> Thank you! >> >> - ----------------------------------------------------------- >> Frank D. Engel, Jr. <fde101@fjrhome.net> >> >> $ ln -s /usr/share/kjvbible /usr/manual >> $ true | cat /usr/manual | grep "John 3:16" >> John 3:16 For God so loved the world, that he gave his only begotten >> Son, that whosoever believeth in him should not perish, but have >> everlasting life. >> $ >> -----BEGIN PGP SIGNATURE----- >> Version: GnuPG v1.2.4 (Darwin) >> >> iD8DBQFB98Dg7aqtWrR9cZoRAhQZAJ9YcDwir3mvcPESGEuduVHWIeXfzwCeMO/+ >> iQ7q3x86VNjjpoywOXr4KJ4= >> =uROx >> -----END PGP SIGNATURE----- >> >> >> >> ___________________________________________________________ >> $0 Web Hosting with up to 120MB web space, 1000 MB Transfer >> 10 Personalized POP and Web E-mail Accounts, and much more. >> Signup at www.doteasy.com >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 4: Don't 'kill -9' the postmaster >> > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend > > - ----------------------------------------------------------- Frank D. Engel, Jr. <fde101@fjrhome.net> $ ln -s /usr/share/kjvbible /usr/manual $ true | cat /usr/manual | grep "John 3:16" John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life. $ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (Darwin) iD8DBQFB/jvW7aqtWrR9cZoRArRKAJoCAOS7Pgqgly5H22eHsKAh9Ks13ACeJw7O 276t3Shl0i+ob+/xP5rYivg= =v8ed -----END PGP SIGNATURE----- ___________________________________________________________ $0 Web Hosting with up to 120MB web space, 1000 MB Transfer 10 Personalized POP and Web E-mail Accounts, and much more. Signup at www.doteasy.com