Export a column in a view without "announcing" it?

[Frank D. Engel, Jr.]

-----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)

iD8DBQFB98Dg7aqtWrR9cZoRAhQZAJ9YcDwir3mvcPESGEuduV HWIeXfzwCeMO/+
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 [url]www.doteasy.com[/url]


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

[Sim Zacks]

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)
>
> iD8DBQFB98Dg7aqtWrR9cZoRAhQZAJ9YcDwir3mvcPESGEuduV HWIeXfzwCeMO/+
> 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 [url]www.doteasy.com[/url]
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

[Dawid Kuroczko]

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.

---------------------------(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

[Frank D. Engel, Jr.]

-----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)
>>
>> iD8DBQFB98Dg7aqtWrR9cZoRAhQZAJ9YcDwir3mvcPESGEuduV HWIeXfzwCeMO/+
>> 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 [url]www.doteasy.com[/url]
>>
>>
>> ---------------------------(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/jvW7aqtWrR9cZoRArRKAJoCAOS7Pgqgly5H22eHsKAh9Ks13AC eJw7O
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 [url]www.doteasy.com[/url]


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

[url]http://archives.postgresql.org[/url]