pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedcalculated identity field in views, again...
11+ messages / 5 participants
[nested] [flat]
* calculated identity field in views, again...
@ 2005-05-03 22:04 Zlatko Matic <zlatko.matic1@sb.t-com.hr>
0 siblings, 2 replies; 11+ messages in thread
From: Zlatko Matic @ 2005-05-03 22:04 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-interfaces
I asked this question several weeks ago, but nobody proposed a solution, so
I am repeating the same question again...
I have an MS Access front-end for a database on PostgreSQL.
I could use pass-through queries as record sources for reports and it works
fine...
Unfortunately, MS Access doesn't allow pass-through queries to be records
sources for subforms.
Therefore I tried to base subforms on regular JET queries on linked tables.
It was too slow...
Then I tried to base subforms on DAO recordset code generated from
pass-through QueryDef objects. Although it worked, it was very unstable...
Now it seems to me that POstgreSQL views are the best solution, but Access
considers views as tables (!) and needs column with unique values.
All those views are complicated queries on several tables, so I can't use
any table's column as primary key. I need a calculated column in the view
that Access will consider as primary key column.
In regular tables, I use bigserial field, but how can I create calculated
bigserial column in a view ?
Thanks.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [INTERFACES] calculated identity field in views, again...
@ 2005-05-04 03:58 Keith Worthington <KeithW@NarrowPathInc.com>
parent: Zlatko Matic <zlatko.matic1@sb.t-com.hr>
1 sibling, 1 reply; 11+ messages in thread
From: Keith Worthington @ 2005-05-04 03:58 UTC (permalink / raw)
To: Zlatko Matic <zlatko.matic1@sb.t-com.hr>; +Cc: pgsql-general@postgresql.org; pgsql-interfaces
Zlatko Matic wrote:
> I asked this question several weeks ago, but nobody proposed a solution,
> so I am repeating the same question again...
> I have an MS Access front-end for a database on PostgreSQL.
> I could use pass-through queries as record sources for reports and it
> works fine...
> Unfortunately, MS Access doesn't allow pass-through queries to be
> records sources for subforms.
> Therefore I tried to base subforms on regular JET queries on linked
> tables. It was too slow...
> Then I tried to base subforms on DAO recordset code generated from
> pass-through QueryDef objects. Although it worked, it was very unstable...
>
> Now it seems to me that POstgreSQL views are the best solution, but
> Access considers views as tables (!) and needs column with unique values.
> All those views are complicated queries on several tables, so I can't
> use any table's column as primary key. I need a calculated column in the
> view that Access will consider as primary key column.
> In regular tables, I use bigserial field, but how can I create
> calculated bigserial column in a view ?
>
> Thanks.
>
> ---------------------------(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
>
>
Another option is to toss the MS Access altogether and program the front
end entirely in VB. That is what we did.
--
Kind Regards,
Keith
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [INTERFACES] calculated identity field in views, again...
@ 2005-05-04 05:11 Greg Stark <gsstark@mit.edu>
parent: Zlatko Matic <zlatko.matic1@sb.t-com.hr>
1 sibling, 1 reply; 11+ messages in thread
From: Greg Stark @ 2005-05-04 05:11 UTC (permalink / raw)
To: Zlatko Matic <zlatko.matic1@sb.t-com.hr>; +Cc: pgsql-general@postgresql.org; pgsql-interfaces
"Zlatko Matic" <zlatko.matic1@sb.t-com.hr> writes:
> In regular tables, I use bigserial field, but how can I create calculated
> bigserial column in a view ?
You would have to create a sequence and reference it with
nextval('sequencename') in your view. But I doubt very much that it will do
anything useful.
It sounds like Access wants to be able to update records by looking them up by
primary key. In that case assigning a new value in your view will make
Postgres make up a brand new number that is utterly useless for finding the
record again later.
--
greg
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [INTERFACES] calculated identity field in views, again...
@ 2005-05-04 08:56 Zlatko Matic <zlatko.matic1@sb.t-com.hr>
parent: Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 11+ messages in thread
From: Zlatko Matic @ 2005-05-04 08:56 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: pgsql-general@postgresql.org; pgsql-interfaces
I will try...if it will be useless, I will quite. Then the only solution
will be make-table query based on nested pass-through query, so I will be
working on local JET tables that will be refreshed from server on each
session. But I would like to avoid local tables, if possible...
How do I create sequence ?
----- Original Message -----
From: "Greg Stark" <gsstark@mit.edu>
To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>
Cc: <pgsql-general@postgresql.org>; <pgsql-interfaces@postgresql.org>
Sent: Wednesday, May 04, 2005 7:11 AM
Subject: Re: [GENERAL] [INTERFACES] calculated identity field in views,
again...
> "Zlatko Matic" <zlatko.matic1@sb.t-com.hr> writes:
>
>> In regular tables, I use bigserial field, but how can I create calculated
>> bigserial column in a view ?
>
> You would have to create a sequence and reference it with
> nextval('sequencename') in your view. But I doubt very much that it will
> do
> anything useful.
>
> It sounds like Access wants to be able to update records by looking them
> up by
> primary key. In that case assigning a new value in your view will make
> Postgres make up a brand new number that is utterly useless for finding
> the
> record again later.
>
> --
> greg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [INTERFACES] calculated identity field in views, again...
@ 2005-05-04 08:58 Zlatko Matic <zlatko.matic1@sb.t-com.hr>
parent: Keith Worthington <KeithW@NarrowPathInc.com>
0 siblings, 1 reply; 11+ messages in thread
From: Zlatko Matic @ 2005-05-04 08:58 UTC (permalink / raw)
To: KeithW@NarrowPathInc.com; +Cc: pgsql-general@postgresql.org; pgsql-interfaces
You mean VB.NET ?
----- Original Message -----
From: "Keith Worthington" <KeithW@NarrowPathInc.com>
To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>
Cc: <pgsql-general@postgresql.org>; <pgsql-interfaces@postgresql.org>
Sent: Wednesday, May 04, 2005 5:58 AM
Subject: Re: [INTERFACES] calculated identity field in views, again...
> Zlatko Matic wrote:
>> I asked this question several weeks ago, but nobody proposed a solution,
>> so I am repeating the same question again...
>> I have an MS Access front-end for a database on PostgreSQL.
>> I could use pass-through queries as record sources for reports and it
>> works fine...
>> Unfortunately, MS Access doesn't allow pass-through queries to be records
>> sources for subforms.
>> Therefore I tried to base subforms on regular JET queries on linked
>> tables. It was too slow...
>> Then I tried to base subforms on DAO recordset code generated from
>> pass-through QueryDef objects. Although it worked, it was very
>> unstable...
>>
>> Now it seems to me that POstgreSQL views are the best solution, but
>> Access considers views as tables (!) and needs column with unique values.
>> All those views are complicated queries on several tables, so I can't use
>> any table's column as primary key. I need a calculated column in the view
>> that Access will consider as primary key column.
>> In regular tables, I use bigserial field, but how can I create calculated
>> bigserial column in a view ?
>>
>> Thanks.
>>
>> ---------------------------(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
>>
>>
>
> Another option is to toss the MS Access altogether and program the front
> end entirely in VB. That is what we did.
>
> --
> Kind Regards,
> Keith
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [INTERFACES] calculated identity field in views, again...
@ 2005-05-04 10:57 Keith Worthington <KeithW@NarrowPathInc.com>
parent: Zlatko Matic <zlatko.matic1@sb.t-com.hr>
0 siblings, 0 replies; 11+ messages in thread
From: Keith Worthington @ 2005-05-04 10:57 UTC (permalink / raw)
To: Zlatko Matic <zlatko.matic1@sb.t-com.hr>; +Cc: pgsql-general@postgresql.org; pgsql-interfaces
>> Zlatko Matic wrote:
>>
>>> I asked this question several weeks ago, but nobody proposed a
>>> solution, so I am repeating the same question again...
>>> I have an MS Access front-end for a database on PostgreSQL.
>>> I could use pass-through queries as record sources for reports and it
>>> works fine...
>>> Unfortunately, MS Access doesn't allow pass-through queries to be
>>> records sources for subforms.
>>> Therefore I tried to base subforms on regular JET queries on linked
>>> tables. It was too slow...
>>> Then I tried to base subforms on DAO recordset code generated from
>>> pass-through QueryDef objects. Although it worked, it was very
>>> unstable...
>>>
>>> Now it seems to me that POstgreSQL views are the best solution, but
>>> Access considers views as tables (!) and needs column with unique
>>> values.
>>> All those views are complicated queries on several tables, so I can't
>>> use any table's column as primary key. I need a calculated column in
>>> the view that Access will consider as primary key column.
>>> In regular tables, I use bigserial field, but how can I create
>>> calculated bigserial column in a view ?
>>>
>>> Thanks.
>>>
>>> ---------------------------(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
>>>
>>>
>>
>> Another option is to toss the MS Access altogether and program the
>> front end entirely in VB. That is what we did.
>>
>> --
>> Kind Regards,
>> Keith
>>
> Zlatko Matic wrote:
> You mean VB.NET ?
Actually we wrote our interface using VB6.
--
Kind Regards,
Keith
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [INTERFACES] calculated identity field in views, again...
@ 2005-05-04 14:16 Bruno Wolff III <bruno@wolff.to>
parent: Zlatko Matic <zlatko.matic1@sb.t-com.hr>
0 siblings, 1 reply; 11+ messages in thread
From: Bruno Wolff III @ 2005-05-04 14:16 UTC (permalink / raw)
To: Zlatko Matic <zlatko.matic1@sb.t-com.hr>; +Cc: Greg Stark <gsstark@mit.edu>; pgsql-general@postgresql.org; pgsql-interfaces
On Wed, May 04, 2005 at 10:56:25 +0200,
Zlatko Matic <zlatko.matic1@sb.t-com.hr> wrote:
> I will try...if it will be useless, I will quite. Then the only solution
> will be make-table query based on nested pass-through query, so I will be
> working on local JET tables that will be refreshed from server on each
> session. But I would like to avoid local tables, if possible...
Didn'y you ask this same question a week or two ago? I am pretty sure
someone told you how to make access work with a view.
> How do I create sequence ?
You can't create a sequence for a view. You would need to have it in a
table that is joined as part of the view.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [INTERFACES] calculated identity field in views, again...
@ 2005-05-04 15:47 Greg Stark <gsstark@mit.edu>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 11+ messages in thread
From: Greg Stark @ 2005-05-04 15:47 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Zlatko Matic <zlatko.matic1@sb.t-com.hr>; Greg Stark <gsstark@mit.edu>; pgsql-general@postgresql.org; pgsql-interfaces
Bruno Wolff III <bruno@wolff.to> writes:
> > How do I create sequence ?
>
> You can't create a sequence for a view. You would need to have it in a
> table that is joined as part of the view.
Sure you can. You can have any expression you want, including nextval('seq')
in your view. The problem is that every time you query it it will provide
different numbers. I can't see that being useful.
--
greg
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [INTERFACES] calculated identity field in views, again...
@ 2005-05-04 16:01 Jeff Eckermann <jeff_eckermann@yahoo.com>
0 siblings, 1 reply; 11+ messages in thread
From: Jeff Eckermann @ 2005-05-04 16:01 UTC (permalink / raw)
To: Zlatko Matic <zlatko.matic1@sb.t-com.hr>; pgsql-general@postgresql.org; pgsql-interfaces
--- Zlatko Matic <zlatko.matic1@sb.t-com.hr> wrote:
> I asked this question several weeks ago, but nobody
> proposed a solution, so
> I am repeating the same question again...
> I have an MS Access front-end for a database on
> PostgreSQL.
> I could use pass-through queries as record sources
> for reports and it works
> fine...
> Unfortunately, MS Access doesn't allow pass-through
> queries to be records
> sources for subforms.
Unless you use unbound form/controls. Which means
handling everything in code, which might work out best
for you, depending on what you want (this is
effectively equivalent to the VB-only option which
someone else mentioned).
> Therefore I tried to base subforms on regular JET
> queries on linked tables.
> It was too slow...
> Then I tried to base subforms on DAO recordset code
> generated from
> pass-through QueryDef objects. Although it worked,
> it was very unstable...
>
> Now it seems to me that POstgreSQL views are the
> best solution, but Access
> considers views as tables (!) and needs column with
> unique values.
AFAIK a composite key (combination of several columns)
should work ok for a primary key for Access. When
linking to the view, just select the columns you want
to use. Or are you saying that you tried this, and it
didn't work?
Alternatively, you could try including in your view
definition the oid column for each of the constituent
tables. If I understand right, oids are globally
unique within your database. This assumes that you
have created your tables with oids, which may not be
the case.
Basing a subform on a mult-table join sounds like odd
database design. Perhaps if you can explain more
about what you are trying to do, people can offer more
suggestions.
> All those views are complicated queries on several
> tables, so I can't use
> any table's column as primary key. I need a
> calculated column in the view
> that Access will consider as primary key column.
> In regular tables, I use bigserial field, but how
> can I create calculated
> bigserial column in a view ?
>
> Thanks.
>
>
> ---------------------------(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
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [INTERFACES] calculated identity field in views, again...
@ 2005-05-04 16:46 Bruno Wolff III <bruno@wolff.to>
parent: Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 11+ messages in thread
From: Bruno Wolff III @ 2005-05-04 16:46 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Zlatko Matic <zlatko.matic1@sb.t-com.hr>; pgsql-general@postgresql.org; pgsql-interfaces
On Wed, May 04, 2005 at 11:47:12 -0400,
Greg Stark <gsstark@mit.edu> wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
>
> > > How do I create sequence ?
> >
> > You can't create a sequence for a view. You would need to have it in a
> > table that is joined as part of the view.
>
> Sure you can. You can have any expression you want, including nextval('seq')
> in your view. The problem is that every time you query it it will provide
> different numbers. I can't see that being useful.
That is more or less what I meant. You can't usefully tie a sequence directly
to a view, the way you can tie one to a table.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [INTERFACES] calculated identity field in views, again...
@ 2005-05-04 20:33 Zlatko Matic <zlatko.matic1@sb.t-com.hr>
parent: Jeff Eckermann <jeff_eckermann@yahoo.com>
0 siblings, 0 replies; 11+ messages in thread
From: Zlatko Matic @ 2005-05-04 20:33 UTC (permalink / raw)
To: Jeff Eckermann <jeff_eckermann@yahoo.com>; pgsql-general@postgresql.org; pgsql-interfaces
Hello. Thanks for answers...
After considering all proposed, I think that it is probably possible to give
MS Acces some composite primary keys while linking views as tables, in order
to help Access not to fall into "#deleted#", but it would take some extra
time to experiment with every view.
In meantime, I successfully implemented solution with local tables. Append
queries based on pass-through queries are triggered and local tables are
refreshed. It seems to be fast and reliable...
Thank you anyway, maybe I will try something with views next time...
----- Original Message -----
From: "Jeff Eckermann" <jeff_eckermann@yahoo.com>
To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>;
<pgsql-general@postgresql.org>; <pgsql-interfaces@postgresql.org>
Sent: Wednesday, May 04, 2005 6:01 PM
Subject: Re: [GENERAL] [INTERFACES] calculated identity field in views,
again...
> --- Zlatko Matic <zlatko.matic1@sb.t-com.hr> wrote:
>> I asked this question several weeks ago, but nobody
>> proposed a solution, so
>> I am repeating the same question again...
>> I have an MS Access front-end for a database on
>> PostgreSQL.
>> I could use pass-through queries as record sources
>> for reports and it works
>> fine...
>> Unfortunately, MS Access doesn't allow pass-through
>> queries to be records
>> sources for subforms.
>
> Unless you use unbound form/controls. Which means
> handling everything in code, which might work out best
> for you, depending on what you want (this is
> effectively equivalent to the VB-only option which
> someone else mentioned).
>
>> Therefore I tried to base subforms on regular JET
>> queries on linked tables.
>> It was too slow...
>> Then I tried to base subforms on DAO recordset code
>> generated from
>> pass-through QueryDef objects. Although it worked,
>> it was very unstable...
>>
>> Now it seems to me that POstgreSQL views are the
>> best solution, but Access
>> considers views as tables (!) and needs column with
>> unique values.
>
> AFAIK a composite key (combination of several columns)
> should work ok for a primary key for Access. When
> linking to the view, just select the columns you want
> to use. Or are you saying that you tried this, and it
> didn't work?
>
> Alternatively, you could try including in your view
> definition the oid column for each of the constituent
> tables. If I understand right, oids are globally
> unique within your database. This assumes that you
> have created your tables with oids, which may not be
> the case.
>
> Basing a subform on a mult-table join sounds like odd
> database design. Perhaps if you can explain more
> about what you are trying to do, people can offer more
> suggestions.
>
>> All those views are complicated queries on several
>> tables, so I can't use
>> any table's column as primary key. I need a
>> calculated column in the view
>> that Access will consider as primary key column.
>> In regular tables, I use bigserial field, but how
>> can I create calculated
>> bigserial column in a view ?
>>
>> Thanks.
>>
>>
>> ---------------------------(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
>>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2005-05-04 20:33 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-03 22:04 calculated identity field in views, again... Zlatko Matic <zlatko.matic1@sb.t-com.hr>
2005-05-04 03:58 ` Keith Worthington <KeithW@NarrowPathInc.com>
2005-05-04 08:58 ` Zlatko Matic <zlatko.matic1@sb.t-com.hr>
2005-05-04 10:57 ` Keith Worthington <KeithW@NarrowPathInc.com>
2005-05-04 05:11 ` Greg Stark <gsstark@mit.edu>
2005-05-04 08:56 ` Zlatko Matic <zlatko.matic1@sb.t-com.hr>
2005-05-04 14:16 ` Bruno Wolff III <bruno@wolff.to>
2005-05-04 15:47 ` Greg Stark <gsstark@mit.edu>
2005-05-04 16:46 ` Bruno Wolff III <bruno@wolff.to>
2005-05-04 16:01 Re: [INTERFACES] calculated identity field in views, again... Jeff Eckermann <jeff_eckermann@yahoo.com>
2005-05-04 20:33 ` Zlatko Matic <zlatko.matic1@sb.t-com.hr>
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