Hi Geoff,
hope you don't mind me unearthing this thread, but I'm curious how you
managed the thing with the PK in the view. I'm quite stuck with a
PostGIS-view without a PK and don't want to create a table instead of the
view. I'm accessing the view from a complex feature (actually non-feature
type that is accessed by a complex feature via Feature Chaining)
I seem to have trouble with "pk_column" : you said you filled it with a PK
from an underlying table that is included in the view. Is the PK defined as
a CONSTRAINT PRIMARY KEY? Did you mean the PK itself is included in the
view? Or just the table? If the PK itself is included - how did you manage
to include it? I tried to create a view containing a PK of one of the tables
like this, e.g.:
first one of the tables of the view:
--------------------------------
CREATE TABLE map_species_ps
(
id_species character varying(20) NOT NULL,
id_ps character varying(20) NOT NULL,
CONSTRAINT map_species_ps_pkey PRIMARY KEY (id_species, id_ps)
)
WITH (
OIDS=FALSE
);
omitting creation of second table "species"
the view:
---------
CREATE OR REPLACE VIEW species_in_ps_PK AS
SELECT map_species_ps.id_species, map_species_ps.id_ps, species.title,
species.href, map_species_ps.map_species_ps_pkey
FROM species
JOIN map_species_ps ON species.idstr::text =
map_species_ps.id_species::text;
error:
-----
ERROR: Column map_species_ps.map_species_ps_pkey does not exist
LINE 2: ...ap_species_ps.id_ps, species.title, species.href, map_specie...
^
I then created the view without the PK. Then I created gt_pk_metadata_table
like this:
CREATE TABLE gt_pk_metadata_table (
table_schema VARCHAR(32) NOT NULL,
table_name VARCHAR(32) NOT NULL,
pk_column VARCHAR(32) NOT NULL,
pk_column_idx INTEGER,
pk_policy VARCHAR(32),
pk_sequence VARCHAR(64),
unique (table_schema, table_name, pk_column),
check (pk_policy in ('sequence', 'assigned', 'autoincrement'))
)
and inserted my view:
INSERT INTO gt_pk_metadata_table(table_schema, table_name, pk_column,
pk_column_idx, pk_policy)
VALUES ('public', 'species_in_ps',
'map_species_ps.map_species_ps_pkey', null, 'assigned');
I'm not sure, though, whether the values are correct? I'm quite at a loss
with how to populate "pk_policy" and "pk_sequence". Do you have a clue which
one I should use?
Now GeoServer reports an error when starting up:
WARN [geotools.jdbc] - Unknown column map_species_ps.map_species_ps_pkey in
table species_in_ps
WARN [geotools.jdbc] - No primary key or unique index found for
species_in_ps.
I have described the problem in more detail in this thread:
http://old.nabble.com/Error-duplicate-mappingName-in-2.0.2-%28but-works-fine-in-2.0.1%29-td29570368.html
http://old.nabble.com/Error-duplicate-mappingName-in-2.0.2-%28but-works-fine-in-2.0.1%29-td29570368.html
Thank you very much in advance for your help!
Barbara
Geoff Clitheroe-2 wrote:
>
> On Thu, May 20, 2010 at 1:03 PM, Geoff Clitheroe
> <[email protected]>wrote:
>
> And to seemingly answer my own question:
>
> It looks like the metadata table is being handled by
> geotools-2.6.1:./library/jdbc/src/main/java/org/geotools/jdbc/MetadataTablePrimaryKeyFinder.java
>
> Which says:
>
> **
> * Looks up primary key information in a metadata table provided by the
> user
> * <p>
> * The table schema will contain:
> * <ul>
> * <li>table_schema (varchar): schema name</li>
> * <li>table_name (varchar): table name</li>
> * <li>pk_column (varchar): column name</li>
> * <li>pk_column_idx (integer): column index if pk is multicolumn
> (nullable)</li>
> * <li>pk_policy (varchar): pk assignment policy: "assigned", "sequence",
> "autogenerated"</li>
> * <li>pk_sequence (varchar): full name of the sequence to be used to
> generate the next value, if
> * any</li>
> * </ul>
> *
> * By default the table is named 'gt_pk_metadata_table'
> *
> * @author Andrea Aime - OpenGeo
> *
> */
>
>
> So I made a table:
>
> CREATE TABLE gt_pk_metadata_table (table_schema varchar(255), table_name
> varchar(255), pk_column varchar(255), pk_column_idx integer, pk_policy
> varchar(255), pk_sequence varchar(255));
>
> add some data
>
> insert into gt_pk_metadata_table(table_schema, table_name, pk_column,
> pk_column_idx, pk_policy, pk_sequence) VALUES ('qrt', 'eventhistory',
> 'eventid', null, 'sequence', 'qrt.event_eventid_seq');
>
> Where eventhistory is my view and eventid is a PK from an underlying table
> that is included in the view.
>
> Then in the datastore config I defined the 'Primary key metadata table' as
> 'public.gt_pk_metadata_table' and now in the GeoRSS output for the
> eventhistory layer the eventid column is being used as the primary key and
> the linking works.
>
> At this stage I don't know if it breaks anything else. Also, I'm just
> reading - no attempts to update data via the view or anything like that.
>
> Cheers,
> Geoff
>
> ------------------------------------------------------------------------------
>
>
> _______________________________________________
> Geoserver-users mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>
>
--
View this message in context:
http://old.nabble.com/postgresql-postgis-views-and-primary-keys-tp28616028p29614681.html
Sent from the GeoServer - User mailing list archive at Nabble.com.
------------------------------------------------------------------------------
This SF.net Dev2Dev email is sponsored by:
Show off your parallel programming skills.
Enter the Intel(R) Threading Challenge 2010.
http://p.sf.net/sfu/intel-thread-sfd
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
