Open-ILS/src/sql/Pg/upgrade/0850.cvm_translated.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('0850', :eg_version);
   4
   5 CREATE OR REPLACE FUNCTION unapi.mra ( obj_id BIGINT, format TEXT,  ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
   6         SELECT  XMLELEMENT(
   7                     name attributes,
   8                     XMLATTRIBUTES(
   9                         CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
  10                         'tag:open-ils.org:U2@mra/' || mra.id AS id,
  11                         'tag:open-ils.org:U2@bre/' || mra.id AS record
  12                     ),
  13                     (SELECT XMLAGG(foo.y)
  14                       FROM (SELECT XMLELEMENT(
  15                                 name field,
  16                                 XMLATTRIBUTES(
  17                                     key AS name,
  18                                     cvm.value AS "coded-value",
  19                                     cvm.id AS "cvmid",
  20                                     rad.filter,
  21                                     rad.sorter
  22                                 ),
  23                                 x.value
  24                             )
  25                            FROM EACH(mra.attrs) AS x
  26                                 JOIN config.record_attr_definition rad ON (x.key = rad.name)
  27                                 LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = x.key AND code = x.value)
  28                         )foo(y)
  29                     )
  30                 )
  31           FROM  metabib.record_attr mra
  32           WHERE mra.id = $1;
  33 $F$ LANGUAGE SQL STABLE;
  34
  35 COMMIT;