2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2007-2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; either version 2
9 * of the License, or (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
18 DROP SCHEMA IF EXISTS asset CASCADE;
24 CREATE TABLE asset.copy_location (
25 id SERIAL PRIMARY KEY,
27 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
28 holdable BOOL NOT NULL DEFAULT TRUE,
29 hold_verify BOOL NOT NULL DEFAULT FALSE,
30 opac_visible BOOL NOT NULL DEFAULT TRUE,
31 circulate BOOL NOT NULL DEFAULT TRUE,
32 CONSTRAINT acl_name_once_per_lib UNIQUE (name, owning_lib)
35 CREATE TABLE asset.copy_location_order
37 id SERIAL PRIMARY KEY,
39 REFERENCES asset.copy_location
41 DEFERRABLE INITIALLY DEFERRED,
43 REFERENCES actor.org_unit
45 DEFERRABLE INITIALLY DEFERRED,
46 position INT NOT NULL DEFAULT 0,
47 CONSTRAINT acplo_once_per_org UNIQUE ( location, org )
50 CREATE TABLE asset.copy (
51 id BIGSERIAL PRIMARY KEY,
52 circ_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
53 creator BIGINT NOT NULL,
54 call_number BIGINT NOT NULL,
55 editor BIGINT NOT NULL,
56 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
57 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
59 status INT NOT NULL DEFAULT 0 REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
60 location INT NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
61 loan_duration INT NOT NULL CHECK ( loan_duration IN (1,2,3) ),
62 fine_level INT NOT NULL CHECK ( fine_level IN (1,2,3) ),
64 circulate BOOL NOT NULL DEFAULT TRUE,
65 deposit BOOL NOT NULL DEFAULT FALSE,
66 ref BOOL NOT NULL DEFAULT FALSE,
67 holdable BOOL NOT NULL DEFAULT TRUE,
68 deposit_amount NUMERIC(6,2) NOT NULL DEFAULT 0.00,
70 barcode TEXT NOT NULL,
76 opac_visible BOOL NOT NULL DEFAULT TRUE,
77 deleted BOOL NOT NULL DEFAULT FALSE,
78 floating BOOL NOT NULL DEFAULT FALSE,
80 status_changed_time TIMESTAMP WITH TIME ZONE,
81 mint_condition BOOL NOT NULL DEFAULT TRUE,
84 CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted = FALSE OR deleted IS FALSE;
85 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
86 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number);
87 CREATE INDEX cp_creator_idx ON asset.copy ( creator );
88 CREATE INDEX cp_editor_idx ON asset.copy ( editor );
89 CREATE RULE protect_copy_delete AS ON DELETE TO asset.copy DO INSTEAD UPDATE asset.copy SET deleted = TRUE WHERE OLD.id = asset.copy.id;
91 CREATE TABLE asset.opac_visible_copies (
92 id BIGINT primary key, -- copy id
96 COMMENT ON TABLE asset.opac_visible_copies IS $$
97 Materialized view of copies that are visible in the OPAC, used by
98 search.query_parser_fts() to speed up OPAC visibility checks on large
99 databases. Contents are maintained by a set of triggers.
101 CREATE INDEX opac_visible_copies_idx1 on asset.opac_visible_copies (record, circ_lib);
103 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
104 RETURNS TRIGGER AS $$
106 IF NEW.status <> OLD.status THEN
107 NEW.status_changed_time := now();
113 CREATE TRIGGER acp_status_changed_trig
114 BEFORE UPDATE ON asset.copy
115 FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
117 CREATE TABLE asset.stat_cat_entry_transparency_map (
118 id BIGSERIAL PRIMARY KEY,
119 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
120 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
121 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
122 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
125 CREATE TABLE asset.stat_cat (
126 id SERIAL PRIMARY KEY,
128 opac_visible BOOL NOT NULL DEFAULT FALSE,
130 required BOOL NOT NULL DEFAULT FALSE,
131 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
134 CREATE TABLE asset.stat_cat_entry (
135 id SERIAL PRIMARY KEY,
136 stat_cat INT NOT NULL,
139 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
142 CREATE TABLE asset.stat_cat_entry_copy_map (
143 id BIGSERIAL PRIMARY KEY,
144 stat_cat INT NOT NULL,
145 stat_cat_entry INT NOT NULL,
146 owning_copy BIGINT NOT NULL,
147 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
149 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
151 CREATE TABLE asset.copy_note (
152 id BIGSERIAL PRIMARY KEY,
153 owning_copy BIGINT NOT NULL,
154 creator BIGINT NOT NULL,
155 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
156 pub BOOL NOT NULL DEFAULT FALSE,
160 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
162 CREATE TABLE asset.uri (
163 id SERIAL PRIMARY KEY,
166 use_restriction TEXT,
167 active BOOL NOT NULL DEFAULT TRUE
170 CREATE TABLE asset.call_number_class (
171 id bigserial PRIMARY KEY,
173 normalizer TEXT NOT NULL DEFAULT 'asset.normalize_generic',
174 field TEXT NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
176 COMMENT ON TABLE asset.call_number_class IS $$
177 Defines the call number normalization database functions in the "normalizer"
178 column and the tag/subfield combinations to use to lookup the call number in
179 the "field" column for a given classification scheme. Tag/subfield combinations
180 are delimited by commas.
183 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
187 sortkey := NEW.label_sortkey;
189 EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
190 quote_literal( NEW.label ) || ')'
191 FROM asset.call_number_class acnc
192 WHERE acnc.id = NEW.label_class
195 NEW.label_sortkey = sortkey;
199 $func$ LANGUAGE PLPGSQL;
201 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
202 # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
203 # thus could probably be considered a derived work, although nothing was
204 # directly copied - but to err on the safe side of providing attribution:
205 # Copyright (C) 2007 LibLime
206 # Licensed under the GPL v2 or later
211 # Converts the callnumber to uppercase
212 # Strips spaces from start and end of the call number
213 # Converts anything other than letters, digits, and periods into underscores
214 # Collapses multiple underscores into a single underscore
215 my $callnum = uc(shift);
216 $callnum =~ s/^\s//g;
217 $callnum =~ s/\s$//g;
218 $callnum =~ s/[^A-Z0-9_.]/_/g;
219 $callnum =~ s/_{2,}/_/g;
222 $func$ LANGUAGE PLPERLU;
224 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
225 # Derived from the Koha C4::ClassSortRoutine::Dewey module
226 # Copyright (C) 2007 LibLime
227 # Licensed under the GPL v2 or later
232 my $init = uc(shift);
236 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
237 my @tokens = split /\.|\s+/, $init;
238 my $digit_group_count = 0;
239 for (my $i = 0; $i <= $#tokens; $i++) {
240 if ($tokens[$i] =~ /^\d+$/) {
241 $digit_group_count++;
242 if (2 == $digit_group_count) {
243 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
244 $tokens[$i] =~ tr/ /0/;
248 my $key = join("_", @tokens);
249 $key =~ s/[^\p{IsAlnum}_]//g;
253 $func$ LANGUAGE PLPERLU;
255 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
259 # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
260 # The author hopes to upload it to CPAN some day, which would make our lives easier
261 use Library::CallNumber::LC;
263 my $callnum = Library::CallNumber::LC->new(shift);
264 return $callnum->normalize();
266 $func$ LANGUAGE PLPERLU;
268 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
269 ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
270 ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab'),
271 ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab')
274 CREATE TABLE asset.call_number (
275 id bigserial PRIMARY KEY,
276 creator BIGINT NOT NULL,
277 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
278 editor BIGINT NOT NULL,
279 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
280 record bigint NOT NULL,
281 owning_lib INT NOT NULL,
283 deleted BOOL NOT NULL DEFAULT FALSE,
284 label_class BIGINT DEFAULT 1 NOT NULL
285 REFERENCES asset.call_number_class(id)
286 DEFERRABLE INITIALLY DEFERRED,
289 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
290 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
291 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
292 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
293 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (upper(label),id,owning_lib);
294 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(label_sortkey);
295 CREATE UNIQUE INDEX asset_call_number_label_once_per_lib ON asset.call_number (record, owning_lib, label) WHERE deleted = FALSE OR deleted IS FALSE;
296 CREATE RULE protect_cn_delete AS ON DELETE TO asset.call_number DO INSTEAD UPDATE asset.call_number SET deleted = TRUE WHERE OLD.id = asset.call_number.id;
297 CREATE TRIGGER asset_label_sortkey_trigger
298 BEFORE UPDATE OR INSERT ON asset.call_number
299 FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
301 CREATE TABLE asset.uri_call_number_map (
302 id BIGSERIAL PRIMARY KEY,
303 uri INT NOT NULL REFERENCES asset.uri (id),
304 call_number INT NOT NULL REFERENCES asset.call_number (id),
305 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
307 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
309 CREATE TABLE asset.call_number_note (
310 id BIGSERIAL PRIMARY KEY,
311 call_number BIGINT NOT NULL,
312 creator BIGINT NOT NULL,
313 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
314 pub BOOL NOT NULL DEFAULT FALSE,
318 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
320 CREATE TABLE asset.copy_template (
321 id SERIAL PRIMARY KEY,
322 owning_lib INT NOT NULL
323 REFERENCES actor.org_unit (id)
324 DEFERRABLE INITIALLY DEFERRED,
325 creator BIGINT NOT NULL
326 REFERENCES actor.usr (id)
327 DEFERRABLE INITIALLY DEFERRED,
328 editor BIGINT NOT NULL
329 REFERENCES actor.usr (id)
330 DEFERRABLE INITIALLY DEFERRED,
331 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
332 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
334 -- columns above this point are attributes of the template itself
335 -- columns after this point are attributes of the copy this template modifies/creates
336 circ_lib INT REFERENCES actor.org_unit (id)
337 DEFERRABLE INITIALLY DEFERRED,
338 status INT REFERENCES config.copy_status (id)
339 DEFERRABLE INITIALLY DEFERRED,
340 location INT REFERENCES asset.copy_location (id)
341 DEFERRABLE INITIALLY DEFERRED,
342 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
343 loan_duration IS NULL OR loan_duration IN (1,2,3)),
344 fine_level INT CONSTRAINT valid_fine_level CHECK (
345 fine_level IS NULL OR loan_duration IN (1,2,3)),
351 deposit_amount NUMERIC(6,2),
361 CREATE VIEW stats.fleshed_copy AS
363 CAST(cp.create_date AS DATE) AS create_date_day,
364 CAST(cp.edit_date AS DATE) AS edit_date_day,
365 DATE_TRUNC('hour', cp.create_date) AS create_date_hour,
366 DATE_TRUNC('hour', cp.edit_date) AS edit_date_hour,
367 cn.label AS call_number_label,
373 JOIN asset.call_number cn ON (cp.call_number = cn.id)
374 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
376 CREATE VIEW stats.fleshed_call_number AS
378 CAST(cn.create_date AS DATE) AS create_date_day,
379 CAST(cn.edit_date AS DATE) AS edit_date_day,
380 DATE_TRUNC('hour', cn.create_date) AS create_date_hour,
381 DATE_TRUNC('hour', cn.edit_date) AS edit_date_hour,
385 FROM asset.call_number cn
386 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
388 CREATE OR REPLACE FUNCTION asset.opac_ou_record_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
393 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record;
395 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
400 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
404 actor.org_unit_descendants(ans.id) d
405 JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
406 JOIN asset.copy cp ON (cp.id = av.id)
410 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
417 $f$ LANGUAGE PLPGSQL;
419 CREATE OR REPLACE FUNCTION asset.opac_lasso_record_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
424 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record;
426 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
431 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
435 actor.org_unit_descendants(ans.id) d
436 JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
437 JOIN asset.copy cp ON (cp.id = av.id)
441 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
448 $f$ LANGUAGE PLPGSQL;
450 CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
455 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record;
457 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
462 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
466 actor.org_unit_descendants(ans.id) d
467 JOIN asset.copy cp ON (cp.circ_lib = d.id)
468 JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
472 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
479 $f$ LANGUAGE PLPGSQL;
481 CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
486 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record;
488 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
493 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
497 actor.org_unit_descendants(ans.id) d
498 JOIN asset.copy cp ON (cp.circ_lib = d.id)
499 JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
503 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
510 $f$ LANGUAGE PLPGSQL;
512 CREATE OR REPLACE FUNCTION asset.record_copy_count ( place INT, record BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
514 IF staff IS TRUE THEN
516 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, record );
518 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, record );
522 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, record );
524 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, record );
530 $f$ LANGUAGE PLPGSQL;
532 CREATE OR REPLACE FUNCTION asset.opac_ou_metarecord_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
537 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record;
539 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
544 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
548 actor.org_unit_descendants(ans.id) d
549 JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
550 JOIN asset.copy cp ON (cp.id = av.id)
551 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
555 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
562 $f$ LANGUAGE PLPGSQL;
564 CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
569 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record;
571 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
576 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
580 actor.org_unit_descendants(ans.id) d
581 JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
582 JOIN asset.copy cp ON (cp.id = av.id)
583 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
587 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
594 $f$ LANGUAGE PLPGSQL;
596 CREATE OR REPLACE FUNCTION asset.staff_ou_metarecord_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
601 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record;
603 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
608 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
612 actor.org_unit_descendants(ans.id) d
613 JOIN asset.copy cp ON (cp.circ_lib = d.id)
614 JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
615 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
619 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
626 $f$ LANGUAGE PLPGSQL;
628 CREATE OR REPLACE FUNCTION asset.staff_lasso_metarecord_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
633 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record;
635 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
640 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
644 actor.org_unit_descendants(ans.id) d
645 JOIN asset.copy cp ON (cp.circ_lib = d.id)
646 JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
647 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
651 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
658 $f$ LANGUAGE PLPGSQL;
660 CREATE OR REPLACE FUNCTION asset.metarecord_copy_count ( place INT, record BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
662 IF staff IS TRUE THEN
664 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, record );
666 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, record );
670 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, record );
672 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, record );
678 $f$ LANGUAGE PLPGSQL;