Hi, Metadata record must suit your your data source and in your case it is the view. Information needed:
- schema of the view - name of the view - column in the view that should be used as primary key - in case of multi-column primary keys, index of first primary key part, and then again the column and index for the next part - pk_policy and pk_sequence are needed only with WFS-T so you can left them null Primary key columns will be eaten out from the output. If you need to get the information of the primary key columns into output you can select it twise into the view create view view_name as (select primary_key PK, primary_key to_be_published, second_column, ....) -Jukka Rahkonen- -----Alkuperäinen viesti----- Lähettäjä: babsip [mailto:[email protected]] Lähetetty: pe 3.9.2010 17:08 Vastaanottaja: [email protected] Aihe: Re: [Geoserver-users] postgresql/postgis views and primary keys 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 ------------------------------------------------------------------------------ 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
