[postgis-users] How to setup a versioned datastore?

[George Silva]

Lee, there is a way to discover the Primary key of a table using a query.
I'll update the function, but I cannot guarantee that they will update on
SVN right away.

The .sql follows attached.

Please, let me know if this fixes the reported bug.

Att.

George

On Tue, Aug 3, 2010 at 4:10 PM, George Silva <georger.silva at gmail.com>wrote:

> Hello Lee, I'm glad it's "trying" to help you.
>
> Yes, the function looks for the PK of each table, when you try yo enable
> it.
>
> I'm at work right now, but an easy way to do it would be creating a
> function which accepts the primary key field as a parameter, which was the
> original design. I'll see if I can find an old version for it and I'll mail
> it to you.
>
> Furthermore, I'll try to fix this bug. Thanks for catching it.
>
> Any other questions you might have, just drop me a line.
>
> George
>
> On Tue, Aug 3, 2010 at 4:02 PM, Lee <quimby5 at yahoo.com> wrote:
>
>> For sure! I was just about to sit down this week and write something very
>> similar.
>>
>> I grabbed it from SVN and trying to make it work on
>> postgres8.4/postgis1.4.   I got as far as select
>> postgis_enable_history(schema,table,geom) but get the following error:
>>
>> ERROR:  more than one row returned by a subquery used as an expression
>> CONTEXT:  SQL statement "SELECT  (SELECT column_name FROM
>> information_schema.key_column_usage WHERE table_schema =  $1  AND table_name
>> =  $2 )"
>> PL/pgSQL function "postgis_enable_history" line 31 at assignment
>>
>>
>> I think maybe it doesn't account for tables with foreign keys??  As a
>> test, I tried this:
>>
>> select * from information_schema.key_column_usage where
>> table_schema='base' and table_name = 'wtr_mainvalves'
>>
>> "midlandgis_test";"base";"mainvalves_pkey";"midlandgis_test";"base";"wtr_mainvalves";"gid";1;
>>
>> "midlandgis_test";"base";"mainvalves_owner_fkey";"midlandgis_test";"base";"wtr_mainvalves";"owner";1;1
>>
>> "midlandgis_test";"base";"mainvalves_status_fkey";"midlandgis_test";"base";"wtr_mainvalves";"status";1;1
>>
>> Furthermore, I tried enabling another table with no foreign keys - no
>> problem there.
>>
>> Looking into the function code, it appears to be looking for a primary key
>> to use in the history table, but I can't immediately see an easy way to
>> differentiate between a pk and fk in the key_column table.
>>
>> Anyone have any ideas?
>>
>> Lee
>>
>>
>> ------------------------------
>> *From:* Rick <graham.rick at gmail.com>
>> *To:* PostGIS Users Discussion <postgis-users at postgis.refractions.net>
>> *Sent:* Tue, August 3, 2010 12:42:05 PM
>> *Subject:* Re: [postgis-users] How to setup a versioned datastore?
>>
>> On Tue, Aug 3, 2010 at 12:17 PM, George Silva <georger.silva at gmail.com>wrote:
>>
>>> Hello Mark,
>>>
>>> Check the SVN
>>>
>>> http://svn.osgeo.org/postgis/tags/1.5.1/extras/history_table/
>>>
>>>
>> Wow!  This looks like just the thing for me.
>>
>> Thanks George.
>>
>> --
>> Cheers!
>> Rick
>>
>> _______________________________________________
>> postgis-users mailing list
>>
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>
>
> --
> George R. C. Silva
>
> Desenvolvimento em GIS
> http://blog.geoprocessamento.net
>



-- 
George R. C. Silva

Desenvolvimento em GIS
http://blog.geoprocessamento.net
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100803/9dc71ac3/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: history_table_v5.sql
Type: application/octet-stream
Size: 9216 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100803/9dc71ac3/attachment.obj>