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,
34 CONSTRAINT acl_name_once_per_lib UNIQUE (name, owning_lib)
37 CREATE TABLE asset.copy_location_order
39 id SERIAL PRIMARY KEY,
41 REFERENCES asset.copy_location
43 DEFERRABLE INITIALLY DEFERRED,
45 REFERENCES actor.org_unit
47 DEFERRABLE INITIALLY DEFERRED,
48 position INT NOT NULL DEFAULT 0,
49 CONSTRAINT acplo_once_per_org UNIQUE ( location, org )
52 CREATE TABLE asset.copy (
53 id BIGSERIAL PRIMARY KEY,
54 circ_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
55 creator BIGINT NOT NULL,
56 call_number BIGINT NOT NULL,
57 editor BIGINT NOT NULL,
58 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
59 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
61 status INT NOT NULL DEFAULT 0 REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
62 location INT NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
63 loan_duration INT NOT NULL CHECK ( loan_duration IN (1,2,3) ),
64 fine_level INT NOT NULL CHECK ( fine_level IN (1,2,3) ),
66 circulate BOOL NOT NULL DEFAULT TRUE,
67 deposit BOOL NOT NULL DEFAULT FALSE,
68 ref BOOL NOT NULL DEFAULT FALSE,
69 holdable BOOL NOT NULL DEFAULT TRUE,
70 deposit_amount NUMERIC(6,2) NOT NULL DEFAULT 0.00,
72 barcode TEXT NOT NULL,
78 opac_visible BOOL NOT NULL DEFAULT TRUE,
79 deleted BOOL NOT NULL DEFAULT FALSE,
80 floating BOOL NOT NULL DEFAULT FALSE,
82 status_changed_time TIMESTAMP WITH TIME ZONE,
83 mint_condition BOOL NOT NULL DEFAULT TRUE,
86 CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted = FALSE OR deleted IS FALSE;
87 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
88 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number);
89 CREATE INDEX cp_creator_idx ON asset.copy ( creator );
90 CREATE INDEX cp_editor_idx ON asset.copy ( editor );
91 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;
93 CREATE TABLE asset.opac_visible_copies (
94 id BIGINT primary key, -- copy id
98 COMMENT ON TABLE asset.opac_visible_copies IS $$
99 Materialized view of copies that are visible in the OPAC, used by
100 search.query_parser_fts() to speed up OPAC visibility checks on large
101 databases. Contents are maintained by a set of triggers.
103 CREATE INDEX opac_visible_copies_idx1 on asset.opac_visible_copies (record, circ_lib);
105 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
106 RETURNS TRIGGER AS $$
108 IF NEW.status <> OLD.status THEN
109 NEW.status_changed_time := now();
115 CREATE TRIGGER acp_status_changed_trig
116 BEFORE UPDATE ON asset.copy
117 FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
119 CREATE TABLE asset.stat_cat_entry_transparency_map (
120 id BIGSERIAL PRIMARY KEY,
121 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
122 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
123 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
124 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
127 CREATE TABLE asset.stat_cat (
128 id SERIAL PRIMARY KEY,
130 opac_visible BOOL NOT NULL DEFAULT FALSE,
132 required BOOL NOT NULL DEFAULT FALSE,
133 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
136 CREATE TABLE asset.stat_cat_entry (
137 id SERIAL PRIMARY KEY,
138 stat_cat INT NOT NULL,
141 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
144 CREATE TABLE asset.stat_cat_entry_copy_map (
145 id BIGSERIAL PRIMARY KEY,
146 stat_cat INT NOT NULL,
147 stat_cat_entry INT NOT NULL,
148 owning_copy BIGINT NOT NULL,
149 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
151 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
153 CREATE TABLE asset.copy_note (
154 id BIGSERIAL PRIMARY KEY,
155 owning_copy BIGINT NOT NULL,
156 creator BIGINT NOT NULL,
157 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
158 pub BOOL NOT NULL DEFAULT FALSE,
162 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
163 CREATE INDEX asset_copy_note_owning_copy_idx ON asset.copy_note ( owning_copy );
165 CREATE TABLE asset.uri (
166 id SERIAL PRIMARY KEY,
169 use_restriction TEXT,
170 active BOOL NOT NULL DEFAULT TRUE
173 CREATE TABLE asset.call_number_class (
174 id bigserial PRIMARY KEY,
176 normalizer TEXT NOT NULL DEFAULT 'asset.normalize_generic',
177 field TEXT NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
179 COMMENT ON TABLE asset.call_number_class IS $$
180 Defines the call number normalization database functions in the "normalizer"
181 column and the tag/subfield combinations to use to lookup the call number in
182 the "field" column for a given classification scheme. Tag/subfield combinations
183 are delimited by commas.
186 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
190 sortkey := NEW.label_sortkey;
192 EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
193 quote_literal( NEW.label ) || ')'
194 FROM asset.call_number_class acnc
195 WHERE acnc.id = NEW.label_class
198 NEW.label_sortkey = sortkey;
202 $func$ LANGUAGE PLPGSQL;
204 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
205 # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
206 # thus could probably be considered a derived work, although nothing was
207 # directly copied - but to err on the safe side of providing attribution:
208 # Copyright (C) 2007 LibLime
209 # Licensed under the GPL v2 or later
214 # Converts the callnumber to uppercase
215 # Strips spaces from start and end of the call number
216 # Converts anything other than letters, digits, and periods into underscores
217 # Collapses multiple underscores into a single underscore
218 my $callnum = uc(shift);
219 $callnum =~ s/^\s//g;
220 $callnum =~ s/\s$//g;
221 $callnum =~ s/[^A-Z0-9_.]/_/g;
222 $callnum =~ s/_{2,}/_/g;
225 $func$ LANGUAGE PLPERLU;
227 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
228 # Derived from the Koha C4::ClassSortRoutine::Dewey module
229 # Copyright (C) 2007 LibLime
230 # Licensed under the GPL v2 or later
235 my $init = uc(shift);
239 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
240 my @tokens = split /\.|\s+/, $init;
241 my $digit_group_count = 0;
242 for (my $i = 0; $i <= $#tokens; $i++) {
243 if ($tokens[$i] =~ /^\d+$/) {
244 $digit_group_count++;
245 if (2 == $digit_group_count) {
246 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
247 $tokens[$i] =~ tr/ /0/;
251 my $key = join("_", @tokens);
252 $key =~ s/[^\p{IsAlnum}_]//g;
256 $func$ LANGUAGE PLPERLU;
258 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
262 # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
263 # The author hopes to upload it to CPAN some day, which would make our lives easier
264 use Library::CallNumber::LC;
266 my $callnum = Library::CallNumber::LC->new(shift);
267 return $callnum->normalize();
269 $func$ LANGUAGE PLPERLU;
271 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
272 ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
273 ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab'),
274 ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab')
277 CREATE TABLE asset.call_number (
278 id bigserial PRIMARY KEY,
279 creator BIGINT NOT NULL,
280 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
281 editor BIGINT NOT NULL,
282 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
283 record bigint NOT NULL,
284 owning_lib INT NOT NULL,
286 deleted BOOL NOT NULL DEFAULT FALSE,
287 label_class BIGINT DEFAULT 1 NOT NULL
288 REFERENCES asset.call_number_class(id)
289 DEFERRABLE INITIALLY DEFERRED,
292 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
293 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
294 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
295 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
296 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(upper(label)),id,owning_lib);
297 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
298 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;
299 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;
300 CREATE TRIGGER asset_label_sortkey_trigger
301 BEFORE UPDATE OR INSERT ON asset.call_number
302 FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
304 CREATE TABLE asset.uri_call_number_map (
305 id BIGSERIAL PRIMARY KEY,
306 uri INT NOT NULL REFERENCES asset.uri (id),
307 call_number INT NOT NULL REFERENCES asset.call_number (id),
308 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
310 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
312 CREATE TABLE asset.call_number_note (
313 id BIGSERIAL PRIMARY KEY,
314 call_number BIGINT NOT NULL,
315 creator BIGINT NOT NULL,
316 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
317 pub BOOL NOT NULL DEFAULT FALSE,
321 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
323 CREATE TABLE asset.copy_template (
324 id SERIAL PRIMARY KEY,
325 owning_lib INT NOT NULL
326 REFERENCES actor.org_unit (id)
327 DEFERRABLE INITIALLY DEFERRED,
328 creator BIGINT NOT NULL
329 REFERENCES actor.usr (id)
330 DEFERRABLE INITIALLY DEFERRED,
331 editor BIGINT NOT NULL
332 REFERENCES actor.usr (id)
333 DEFERRABLE INITIALLY DEFERRED,
334 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
335 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
337 -- columns above this point are attributes of the template itself
338 -- columns after this point are attributes of the copy this template modifies/creates
339 circ_lib INT REFERENCES actor.org_unit (id)
340 DEFERRABLE INITIALLY DEFERRED,
341 status INT REFERENCES config.copy_status (id)
342 DEFERRABLE INITIALLY DEFERRED,
343 location INT REFERENCES asset.copy_location (id)
344 DEFERRABLE INITIALLY DEFERRED,
345 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
346 loan_duration IS NULL OR loan_duration IN (1,2,3)),
347 fine_level INT CONSTRAINT valid_fine_level CHECK (
348 fine_level IS NULL OR loan_duration IN (1,2,3)),
354 deposit_amount NUMERIC(6,2),
364 CREATE VIEW stats.fleshed_copy AS
366 CAST(cp.create_date AS DATE) AS create_date_day,
367 CAST(cp.edit_date AS DATE) AS edit_date_day,
368 DATE_TRUNC('hour', cp.create_date) AS create_date_hour,
369 DATE_TRUNC('hour', cp.edit_date) AS edit_date_hour,
370 cn.label AS call_number_label,
376 JOIN asset.call_number cn ON (cp.call_number = cn.id)
377 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
379 CREATE VIEW stats.fleshed_call_number AS
381 CAST(cn.create_date AS DATE) AS create_date_day,
382 CAST(cn.edit_date AS DATE) AS edit_date_day,
383 DATE_TRUNC('hour', cn.create_date) AS create_date_hour,
384 DATE_TRUNC('hour', cn.edit_date) AS edit_date_hour,
388 FROM asset.call_number cn
389 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
391 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$
396 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;
398 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
403 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
407 actor.org_unit_descendants(ans.id) d
408 JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
409 JOIN asset.copy cp ON (cp.id = av.id)
413 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
420 $f$ LANGUAGE PLPGSQL;
422 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$
427 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;
429 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
434 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
438 actor.org_unit_descendants(ans.id) d
439 JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
440 JOIN asset.copy cp ON (cp.id = av.id)
444 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
451 $f$ LANGUAGE PLPGSQL;
453 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$
458 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;
460 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
465 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
469 actor.org_unit_descendants(ans.id) d
470 JOIN asset.copy cp ON (cp.circ_lib = d.id)
471 JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
475 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
482 $f$ LANGUAGE PLPGSQL;
484 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$
489 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;
491 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
496 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
500 actor.org_unit_descendants(ans.id) d
501 JOIN asset.copy cp ON (cp.circ_lib = d.id)
502 JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
506 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
513 $f$ LANGUAGE PLPGSQL;
515 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$
517 IF staff IS TRUE THEN
519 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, record );
521 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, record );
525 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, record );
527 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, record );
533 $f$ LANGUAGE PLPGSQL;
535 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$
540 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;
542 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
547 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
551 actor.org_unit_descendants(ans.id) d
552 JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
553 JOIN asset.copy cp ON (cp.id = av.id)
554 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
558 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
565 $f$ LANGUAGE PLPGSQL;
567 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$
572 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;
574 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
579 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
583 actor.org_unit_descendants(ans.id) d
584 JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
585 JOIN asset.copy cp ON (cp.id = av.id)
586 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
590 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
597 $f$ LANGUAGE PLPGSQL;
599 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$
604 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;
606 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
611 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
615 actor.org_unit_descendants(ans.id) d
616 JOIN asset.copy cp ON (cp.circ_lib = d.id)
617 JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
618 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
622 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
629 $f$ LANGUAGE PLPGSQL;
631 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$
636 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;
638 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
643 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
647 actor.org_unit_descendants(ans.id) d
648 JOIN asset.copy cp ON (cp.circ_lib = d.id)
649 JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
650 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
654 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
661 $f$ LANGUAGE PLPGSQL;
663 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$
665 IF staff IS TRUE THEN
667 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, record );
669 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, record );
673 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, record );
675 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, record );
681 $f$ LANGUAGE PLPGSQL;