From a1bc77696459e4865bd46db9283670babbfee29c Mon Sep 17 00:00:00 2001 From: dbs Date: Sun, 22 Jun 2008 18:19:05 +0000 Subject: [PATCH] Move aggregate functions into their own file and add DROP statements. Resolves dependencies and makes the schema creation more robust. git-svn-id: svn://svn.open-ils.org/ILS/trunk@9909 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../src/sql/Pg/002.functions.aggregate.sql | 89 +++++++++++++++++++ Open-ILS/src/sql/Pg/020.schema.functions.sql | 54 +---------- Open-ILS/src/sql/Pg/build-db.sh | 1 + 3 files changed, 91 insertions(+), 53 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/002.functions.aggregate.sql diff --git a/Open-ILS/src/sql/Pg/002.functions.aggregate.sql b/Open-ILS/src/sql/Pg/002.functions.aggregate.sql new file mode 100644 index 0000000000..b6d308f77b --- /dev/null +++ b/Open-ILS/src/sql/Pg/002.functions.aggregate.sql @@ -0,0 +1,89 @@ +/* + * Copyright (C) 2004-2008 Georgia Public Library Service + * Copyright (C) 2008 Equinox Software, Inc. + * Mike Rylander + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU General Public License + * as published by the Free Software Foundation; either version 2 + * of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + */ + +BEGIN; + +DROP AGGREGATE IF EXISTS array_accum(anyelement) CASCADE; + +CREATE AGGREGATE array_accum ( + sfunc = array_append, + basetype = anyelement, + stype = anyarray, + initcond = '{}' +); + +CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement AS $$ + SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END; +$$ LANGUAGE SQL STABLE; + +DROP AGGREGATE IF EXISTS public.first(anyelement) CASCADE; + +CREATE AGGREGATE public.first ( + sfunc = public.first_agg, + basetype = anyelement, + stype = anyelement +); + +CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) RETURNS anyelement AS $$ + SELECT $2; +$$ LANGUAGE SQL STABLE; + +DROP AGGREGATE IF EXISTS public.last(anyelement) CASCADE; + +CREATE AGGREGATE public.last ( + sfunc = public.last_agg, + basetype = anyelement, + stype = anyelement +); + +CREATE OR REPLACE FUNCTION public.text_concat ( TEXT, TEXT ) RETURNS TEXT AS $$ +SELECT + CASE WHEN $1 IS NULL + THEN $2 + WHEN $2 IS NULL + THEN $1 + ELSE $1 || ' ' || $2 + END; +$$ LANGUAGE SQL STABLE; + +DROP AGGREGATE IF EXISTS public.agg_text(text) CASCADE; + +CREATE AGGREGATE public.agg_text ( + sfunc = public.text_concat, + basetype = text, + stype = text +); + +CREATE OR REPLACE FUNCTION public.tsvector_concat ( tsvector, tsvector ) RETURNS tsvector AS $$ +SELECT + CASE WHEN $1 IS NULL + THEN $2 + WHEN $2 IS NULL + THEN $1 + ELSE $1 || ' ' || $2 + END; +$$ LANGUAGE SQL STABLE; + +DROP AGGREGATE IF EXISTS public.agg_tsvector(tsvector) CASCADE; + +CREATE AGGREGATE public.agg_tsvector ( + sfunc = public.tsvector_concat, + basetype = tsvector, + stype = tsvector +); + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/020.schema.functions.sql b/Open-ILS/src/sql/Pg/020.schema.functions.sql index 7796c01577..9ea4e8d092 100644 --- a/Open-ILS/src/sql/Pg/020.schema.functions.sql +++ b/Open-ILS/src/sql/Pg/020.schema.functions.sql @@ -125,59 +125,7 @@ CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT, INT ) RETURNS TEXT AS SELECT SUBSTRING(call_number_dewey($1) FROM 1 FOR $2); $$ LANGUAGE SQL STRICT IMMUTABLE; -CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement AS $$ - SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END; -$$ LANGUAGE SQL STABLE; - -CREATE AGGREGATE public.first ( - sfunc = public.first_agg, - basetype = anyelement, - stype = anyelement -); - -CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) RETURNS anyelement AS $$ - SELECT $2; -$$ LANGUAGE SQL STABLE; - -CREATE AGGREGATE public.last ( - sfunc = public.last_agg, - basetype = anyelement, - stype = anyelement -); - -CREATE OR REPLACE FUNCTION public.text_concat ( TEXT, TEXT ) RETURNS TEXT AS $$ -SELECT - CASE WHEN $1 IS NULL - THEN $2 - WHEN $2 IS NULL - THEN $1 - ELSE $1 || ' ' || $2 - END; -$$ LANGUAGE SQL STABLE; - -CREATE AGGREGATE public.agg_text ( - sfunc = public.text_concat, - basetype = text, - stype = text -); - -CREATE OR REPLACE FUNCTION public.tsvector_concat ( tsvector, tsvector ) RETURNS tsvector AS $$ -SELECT - CASE WHEN $1 IS NULL - THEN $2 - WHEN $2 IS NULL - THEN $1 - ELSE $1 || ' ' || $2 - END; -$$ LANGUAGE SQL STABLE; - -CREATE AGGREGATE public.agg_tsvector ( - sfunc = public.tsvector_concat, - basetype = tsvector, - stype = tsvector -); - -CREATE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$ +CREATE OR REPLACE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$ BEGIN RETURN $1::regclass; END; diff --git a/Open-ILS/src/sql/Pg/build-db.sh b/Open-ILS/src/sql/Pg/build-db.sh index c73cce0f30..6541b40567 100755 --- a/Open-ILS/src/sql/Pg/build-db.sh +++ b/Open-ILS/src/sql/Pg/build-db.sh @@ -6,6 +6,7 @@ PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 000.english.pg$6.fts-config.sql PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 001.schema.offline.sql PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 002.schema.config.sql +PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 002.functions.aggregate.sql PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 002.functions.config.sql PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 005.schema.actors.sql PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 006.schema.permissions.sql -- 2.43.2