Ok. I think you can still turn your original view into one that is recognized by GeoServer, like this:
CREATE VIEW view_my_point_feature_with_files AS SELECT gid, name, description, files, the_geom FROM my_feature, resource_file WHERE resource_file.from_table='my_point_feature' AND resource_file.with_id=gid; I just added gid because I think GeoServer needs a numeric id. Yours, S. 2011/3/19 Òscar Vidal Calbet <oscar...@gmail.com>: > Hi Stefan, > > Thanks for the answer. You are right, my explanation is somewhat confusing. > I think is easier just post the following example code: > > =========================================== > -- > CREATE TYPE file AS ( > file_name varchar(64), > file_description varchar(256), > file_url varchar(256), > file_type varchar(32), > file_size decimal, > file_updated date > ); > -- > CREATE TABLE resource_file > ( > id serial NOT NULL, > from_table varchar(32) NOT NULL, > with_id integer NOT NULL, > files file[], > CONSTRAINT resource_file_pkey PRIMARY KEY (id) > ); > -- > INSERT INTO resource_file_test(id, from_table, with_id, files) > VALUES (nextval('resource_file_id_seq'), 'my_point_feature', '54', > ARRAY[[row('my_file.pdf', 'test', > '/where/is/the/file/my_file01.pdf', 'pdf', 1.5, > statement_timestamp())::file], > [row('my_file.jpg', 'test', > '/where/is/the/file/my_file02.jpg', 'image', 1.5, > statement_timestamp())::file]]); > -- > CREATE VIEW view_my_point_feature_with_files > AS SELECT name, description, files, the_geom > FROM my_feature, resource_file > WHERE resource_file.from_table='my_point_feature' AND > resource_file.with_id=gid > > =========================================== > Hope its clear now. > > Stefan, I tried already the UNNEST() function > > SELECT name, description, files, unnest(the_geom) > > But it is giving me back a one line per each file, my objective is > receive N files in the same row. > > I think my approach is wrong. That is because Im using objects ("TYPE > file" and ARRAY) that are not supported like attributes of spatial > entity, by the WMS or geoserver. This objects are not standards. > > Right now Im making another New Approach, its not so elegant, but its working. > > =========================================== > -- > CREATE TYPE type_file AS ENUM ('PDF', 'IMAGE', 'VIDEO', 'GPX', 'ZIP'); > -- > CREATE TABLE resource_file > ( > id serial NOT NULL, > from_table varchar(32) NOT NULL, > with_id integer NOT NULL, > file_name varchar(64) NOT NULL, > file_description varchar(256) NOT NULL, > file_host varchar(256) NOT NULL, > file_path varchar(256) NOT NULL, > file_type type_file NOT NULL, > file_size decimal NOT NULL, > file_updated date, > CONSTRAINT resource_file_pkey PRIMARY KEY (id) > ); > -- > INSERT INTO resource_file(id, from_table, with_id, file_name, > file_description, file_host, file_path, file_type, file_size, > file_updated) > VALUES (nextval('resource_file_id_seq'),'my_point_feature',54, > 'file.pdf', 'teste', > 'http://localhost/','where/filesFTP/are/050334.pdf','PDF',1.5,statement_timestamp()); > -- > INSERT INTO resource_file(id, from_table, with_id, file_name, > file_description, file_host, file_path, file_type, file_size, > file_updated) > VALUES > (nextval('resource_file_id_seq'),'my_point_feature',54,'file.jpg','teste', > 'http://localhost/', > 'where/filesFTP/are/050334.jpg','IMAGE',1.5,statement_timestamp()); > -- > CREATE or REPLACE FUNCTION getFilesHTML(text, int) RETURNS text AS $$ > DECLARE > _from_table alias for $1; > _with_id alias for $2; > r resource_file%rowtype; > HTML text := ''; > url text := ''; > description text := ''; > BEGIN > FOR r IN SELECT * FROM resource_file WHERE from_table = > _from_table AND with_id = _with_id > LOOP > url := r.file_host || r.file_path; > description := r.file_description ||' ('|| r.file_updated ||')'; > HTML:= HTML || '<a href="'|| url ||'" title="'|| description ||'" > target="_blank" >'|| r.file_name ||'</a>'; > END LOOP; > RETURN HTML; > END; > $$ LANGUAGE plpgsql; > -- > CREATE VIEW view_my_point_feature_with_files > AS SELECT name, description, getFilesHTML('my_point_feature', gid) as > HTMLfiles, the_geom > FROM my_point_feature; > =========================================== > > So, in one request, containing one row per feature, I can show/link the N > files. > The 'bad' thing, is that I'm mixing already the HTML with the data, in > the database but I'm open to new suggestions/approaches. > > Thanks! > Oscar > > 2011/3/19 Stefan Keller <sfkel...@gmail.com>: >> Hi Oscar >> >> I don't understand what you really did. >> >> 1. GeoServer can only serve tables with id and geometry. >> >> 2. Can you specify the view you defined and the join? >> >> To me that would work too (except that the geometry attribute is lacking): >> >> CREATE VIEW resource_file_v(id, files) AS ( >> SELECT id, unnest(files) AS files FROM resource_file >> ); >> >> Yours, S. >> >> 2011/3/15 Òscar Vidal Calbet <oscar...@gmail.com>: >>> Hey everyone, >>> >>> I published some WMS services in Geoserver from PostGIS database and I >>> need to make a GetFeaureInfo that gives me data from different tables, >>> which can be solved by creating a VIEW. The problem is that I need >>> that one of the fields in the VIEW be allowed to give me N entities >>> referring to N files (strings with the path) related with the feature. >>> To solve that in Postgres I created an ARRAY of Composite Types in >>> the table: >>> >>> CREATE TYPE file AS ( >>> >>> file_name varchar(64), >>> >>> file_url varchar(256), >>> >>> ... >>> ); >>> >>> >>> >>> CREATE TABLE resource_file >>> >>> ( >>> >>> id serial NOT NULL, >>> >>> from_table varchar(32) NOT NULL, >>> >>> with_id integer NOT NULL, >>> >>> files ARRAY file, >>> >>> CONSTRAINT resource_file_pkey PRIMARY KEY (id) >>> >>> ) >>> >>> So I'm creating a VIEW with the feature JOIN resource_file to get the >>> info of the feature with all the (N) files associated. But when I'm >>> publishing the VIEW, apparently there is no error but the ARRAY field >>> is missing. I check in the OpenGIS® Web Map Server Implementation >>> Specification >>> , but I didn't find anything about if it can be done or not. >>> >>> Is it possible? >>> I would like to solve the problem by having only one request to the >>> server to get the N files. Any suggestions? >>> Maybe I can create a function in Postgres that builds already the >>> content (a string with html tags) to show the N files and pass this to >>> the view? >>> >>> Thanks in advance! >>> oscar >>> _______________________________________________ >>> postgis-users mailing list >>> postgis-users@postgis.refractions.net >>> http://postgis.refractions.net/mailman/listinfo/postgis-users >>> >> > _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users