POE Paste with lovely alien head...

No paste!

from "JBoyer" at 64.57.241.14
Patron / staff search issue for miker

format options below
PATRON:

-- bib search: #CD_documentLength #CD_meanHarmonic #CD_uniqueWords core_limit(10000) badge_orgs(1,72,73) estimation_strategy(inclusion) skip_check(0) check_limit(1000) sort(poprel) title:bag of bones site(STLIB-IND) depth(2)
        WITH w AS (


WITH xd7a79f8_title_xq AS (SELECT
      (to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2218$bag$_2218$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2218$bag$_2218$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
      (to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2218$of$_2218$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2218$of$_2218$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
      (to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2218$bones$_2218$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2218$bones$_2218$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) AS tsq,
      (to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2218$bag$_2218$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2218$bag$_2218$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
      (to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2218$of$_2218$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2218$of$_2218$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
      (to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2218$bones$_2218$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2218$bones$_2218$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) AS tsq_rank ),lang_with AS (SELECT id FROM config.coded_value_map WHERE ctype = 'item_lang' AND code = $_2218$eng$_2218$),        pop_with AS (
            SELECT  record,
                    ARRAY_AGG(badge) AS badges,
                    SUM(s.score::NUMERIC*b.weight::NUMERIC)/SUM(b.weight::NUMERIC) AS total_score
              FROM  rating.record_badge_score s
                    JOIN rating.badge b ON (
                        b.id = s.badge
 AND b.scope = ANY ('{1,72,73}')) GROUP BY 1)
,c_attr AS (SELECT (ARRAY_TO_STRING(ARRAY[c_attrs,search.calculate_visibility_attribute_test('circ_lib','{73}',FALSE)],'&'))::query_int AS vis_test FROM asset.patron_default_visibility_mask() x)
,b_attr AS (SELECT (ARRAY_TO_STRING(ARRAY[b_attrs,search.calculate_visibility_attribute_test('luri_org','{1,72,73}',FALSE)],'&'))::query_int AS vis_test FROM asset.patron_default_visibility_mask() x)
SELECT  id,
        rel,
        CASE WHEN cardinality(records) = 1 THEN records[1] ELSE NULL END AS record,
        NULL::INT AS total,
        NULL::INT AS checked,
        NULL::INT AS visible,
        NULL::INT AS deleted,
        NULL::INT AS excluded,
        badges,
        popularity
  FROM  (SELECT m.source AS id,
                ARRAY[m.source] AS records,
                (AVG(
          (COALESCE(ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', xd7a79f8_title.index_vector, xd7a79f8_title.tsq_rank, 14) * xd7a79f8_title.weight, 0.0))
        )+1 * COALESCE( NULLIF( FIRST(mrv.vlist @> ARRAY[lang_with.id]), FALSE )::INT * 5, 1))::NUMERIC AS rel,
                1.0/(( (AVG(
          (COALESCE(ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', xd7a79f8_title.index_vector, xd7a79f8_title.tsq_rank, 14) * xd7a79f8_title.weight, 0.0))
        )+1 * COALESCE( NULLIF( FIRST(mrv.vlist @> ARRAY[lang_with.id]), FALSE )::INT * 5, 1)) ) * (1.0 + (AVG(COALESCE(pop_with.total_score::NUMERIC,0.0::NUMERIC)) * 0.02::NUMERIC)))::NUMERIC AS rank,
                FIRST(pubdate_t.value) AS tie_break,
                STRING_AGG(ARRAY_TO_STRING(pop_with.badges,','),',') AS badges,
                AVG(COALESCE(pop_with.total_score::NUMERIC,0.0::NUMERIC))::NUMERIC(2,1) AS popularity
          FROM  metabib.metarecord_source_map m

        LEFT JOIN (
          SELECT fe.*, fe_weight.weight, xd7a79f8_title_xq.tsq, xd7a79f8_title_xq.tsq_rank /* search */
            FROM  metabib.title_field_entry AS fe
              JOIN config.metabib_field AS fe_weight ON (fe_weight.id = fe.field)
            JOIN xd7a79f8_title_xq ON (fe.index_vector @@ xd7a79f8_title_xq.tsq)
        ) AS xd7a79f8_title ON (m.source = xd7a79f8_title.source)

                INNER JOIN metabib.record_attr_vector_list mrv ON m.source = mrv.source
                INNER JOIN biblio.record_entry bre ON m.source = bre.id
                LEFT JOIN pop_with ON ( m.source = pop_with.record )
                LEFT JOIN metabib.record_sorter pubdate_t ON m.source = pubdate_t.source AND attr = 'pubdate'
                ,lang_with
                ,c_attr
                ,b_attr
          WHERE 1=1
                AND (
          (xd7a79f8_title.id IS NOT NULL)
        )
        AND (
          (EXISTS (SELECT 1 FROM asset.copy_vis_attr_cache WHERE record = m.source AND vis_attr_vector @@ c_attr.vis_test)) OR ((b_attr.vis_test IS NULL OR bre.vis_attr_vector @@ b_attr.vis_test))
        )
          GROUP BY 1
          ORDER BY 4 ASC NULLS LAST,  5 DESC NULLS LAST, 3 DESC
          LIMIT 10000
        ) AS core_query
) (SELECT * FROM w LIMIT 1000 OFFSET 0)
        UNION ALL
  SELECT NULL,NULL,NULL,COUNT(*),COUNT(*),COUNT(*),0,0,NULL,NULL FROM w;



STAFF:

-- bib search: #CD_documentLength #CD_meanHarmonic #CD_uniqueWords #staff core_limit(10000) badge_orgs(1,72,73) estimation_strategy(inclusion) skip_check(0) check_limit(1000) pref_ou(ZIONS-HUS) sort(poprel) title:bag of bones site(STLIB-IND) depth(2)
        WITH w AS (


WITH xd36afb0_title_xq AS (SELECT
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2214$bag$_2214$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2214$bag$_2214$)),E'(?:\\s+|:)','&','g'),'&|')|| ')', '()'), '')))&&
      (to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2214$of$_2214$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2214$of$_2214$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
      (to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2214$bones$_2214$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2214$bones$_2214$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) AS tsq,
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2214$bag$_2214$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2214$bag$_2214$)),E'(?:\\s+|:)','&','g'),'&|')|| ')', '()'), ''))) ||
      (to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2214$of$_2214$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2214$of$_2214$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
      (to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2214$bones$_2214$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_2214$bones$_2214$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) AS tsq_rank ),lang_with AS (SELECT id FROM config.coded_value_map WHERE ctype = 'item_lang' AND code = $_2214$eng$_2214$),        pop_with AS (
            SELECT  record,
                    ARRAY_AGG(badge) AS badges,
                    SUM(s.score::NUMERIC*b.weight::NUMERIC)/SUM(b.weight::NUMERIC) AS total_score
              FROM  rating.record_badge_score s
                    JOIN rating.badge b ON (
                        b.id = s.badge
 AND b.scope = ANY ('{1,72,73}')) GROUP BY 1)
,c_attr AS (SELECT (ARRAY_TO_STRING(ARRAY[search.calculate_visibility_attribute_test('circ_lib','{73}',FALSE)],'&'))::query_int AS vis_test FROM asset.patron_default_visibility_mask() x)
,b_attr AS (SELECT (ARRAY_TO_STRING(ARRAY[search.calculate_visibility_attribute_test('luri_org','{1,72,73}',FALSE)],'&'))::query_int AS vis_test FROM asset.patron_default_visibility_mask() x)
SELECT  id,
        rel,
        CASE WHEN cardinality(records) = 1 THEN records[1] ELSE NULL END AS record,
        NULL::INT AS total,
        NULL::INT AS checked,
        NULL::INT AS visible,
        NULL::INT AS deleted,
        NULL::INT AS excluded,
        badges,
        popularity
  FROM  (SELECT m.source AS id,
                ARRAY[m.source] AS records,
                (AVG(
          (COALESCE(ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', xd36afb0_title.index_vector, xd36afb0_title.tsq_rank, 14) * xd36afb0_title.weight, 0.0))
        )+1 * COALESCE( NULLIF( FIRST(mrv.vlist @> ARRAY[lang_with.id]), FALSE )::INT * 5, 1))::NUMERIC AS rel,
                1.0/(( (AVG(
          (COALESCE(ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', xd36afb0_title.index_vector, xd36afb0_title.tsq_rank, 14) * xd36afb0_title.weight, 0.0))
        )+1 * COALESCE( NULLIF( FIRST(mrv.vlist @> ARRAY[lang_with.id]), FALSE )::INT * 5, 1)) ) * (1.0 + (AVG(COALESCE(pop_with.total_score::NUMERIC,0.0::NUMERIC)) * 0.02::NUMERIC)))::NUMERIC AS rank,
                FIRST(pubdate_t.value) AS tie_break,
                STRING_AGG(ARRAY_TO_STRING(pop_with.badges,','),',') AS badges,
                AVG(COALESCE(pop_with.total_score::NUMERIC,0.0::NUMERIC))::NUMERIC(2,1) AS popularity
          FROM  metabib.metarecord_source_map m

        LEFT JOIN (
          SELECT fe.*, fe_weight.weight, xd36afb0_title_xq.tsq, xd36afb0_title_xq.tsq_rank /* search */
            FROM  metabib.title_field_entry AS fe
              JOIN config.metabib_field AS fe_weight ON (fe_weight.id = fe.field)
            JOIN xd36afb0_title_xq ON (fe.index_vector @@ xd36afb0_title_xq.tsq)
        ) AS xd36afb0_title ON (m.source = xd36afb0_title.source)

                INNER JOIN metabib.record_attr_vector_list mrv ON m.source = mrv.source
                INNER JOIN biblio.record_entry bre ON m.source = bre.id
                LEFT JOIN pop_with ON ( m.source = pop_with.record )
                LEFT JOIN metabib.record_sorter pubdate_t ON m.source = pubdate_t.source AND attr = 'pubdate'
                ,lang_with
                ,c_attr
                ,b_attr
          WHERE 1=1
                AND (
          (xd36afb0_title.id IS NOT NULL)
        )
        AND (
          ((EXISTS (SELECT 1 FROM asset.copy_vis_attr_cache WHERE record = m.source AND vis_attr_vector @@ c_attr.vis_test) 
      OR NOT EXISTS (SELECT 1 FROM asset.copy_vis_attr_cache WHERE record = m.source))) OR (((b_attr.vis_test IS NULL OR bre.vis_attr_vector @@ b_attr.vis_test) 
      OR NOT ( int4range(0,268435455,'[]') @> ANY(bre.vis_attr_vector) )))
        )
          GROUP BY 1
          ORDER BY 4 ASC NULLS LAST,  5 DESC NULLS LAST, 3 DESC
          LIMIT 10000
        ) AS core_query
) (SELECT * FROM w LIMIT 1000 OFFSET 0)
        UNION ALL
  SELECT NULL,NULL,NULL,COUNT(*),COUNT(*),COUNT(*),0,0,NULL,NULL FROM w;






perl stuff: (might be helpful for others but not perfect)



Valid HTML 4.01!

Pastebot is powered by POE.