Обсуждение: Re: [INTERFACES] calculated identity field in views, again...

Поиск
Список
Период
Сортировка

Re: [INTERFACES] calculated identity field in views, again...

От
Jeff Eckermann
Дата:
--- 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

Re: [INTERFACES] calculated identity field in views, again...

От
"Zlatko Matic"
Дата:
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)
>


Re: [INTERFACES] calculated identity field in views,

От
Ragnar Hafstað
Дата:
On Wed, 2005-05-04 at 09:01 -0700, Jeff Eckermann wrote:

>   If I understand right, oids are globally
> unique within your database.

I am affraid not

gnari



Re: [INTERFACES] calculated identity field in views,

От
"Joshua D. Drake"
Дата:
Ragnar Hafstað wrote:
> On Wed, 2005-05-04 at 09:01 -0700, Jeff Eckermann wrote:
>
>
>>  If I understand right, oids are globally
>>unique within your database.
>
>
> I am affraid not

oids are globally unique within the catalog.

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


Re: [INTERFACES] calculated identity field in views,

От
Tom Lane
Дата:
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Ragnar Hafsta� wrote:
>> On Wed, 2005-05-04 at 09:01 -0700, Jeff Eckermann wrote:
>>> If I understand right, oids are globally
>>> unique within your database.
>>
>> I am affraid not

> oids are globally unique within the catalog.

... until the OID generator wraps around, and then they aren't so
unique anymore.

You can enforce uniqueness within a particular table by creating
a unique index on the OID column, but that's about as much as
you can guarantee.

            regards, tom lane