Now I see. But then I would use filetype helper functions like below. Yours, S.
CREATE or REPLACE FUNCTION filetype_to_href(filetype) RETURNS text AS $$ DECLARE file alias for $1; href text := ''; BEGIN RETURN '<a href="'|| file.file_url ||'" target="_blank" >'|| file.file_name ||'</a>'; END; $$ LANGUAGE plpgsql; -- Test: SELECT filetype_to_href(files[1]) FROM resource_file; CREATE or REPLACE FUNCTION filetype_array_to_href(filetype[]) RETURNS text AS $$ DECLARE files alias for $1; href_list text := ''; BEGIN FOR n IN 1 .. array_upper($1, 1) LOOP href_list := href_list || filetype_to_href(files[n]) || ' '; END LOOP; RETURN href_list; END; $$ LANGUAGE plpgsql; -- Test: SELECT id, filetype_array_to_href(files) FROM resource_file; 2011/3/19 Òscar Vidal Calbet <oscar...@gmail.com>: > Thanks Stefan. > > I tried already, but is happening the same. > Geoserver is only publishing the fields "gid, name, description, > the_geom" but no the "files". > "files" is a ARRAY field with elements of a TYPE created for me. > > thanks, > oscar > > 2011/3/19 Stefan Keller <sfkel...@gmail.com>: >> 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