Hi there,
I have two tables that I want to join together as well as an aggregate
function that takes one of the columns and makes one string out of many
rows of info.
One part of the aggregate function (the one that puts two strings
together) has been re-written cause I have to get lower case words first
and upper case words last. I am new to the making functions
but that particular function works on its own. However when I try to run
an SQL query with the aggregate function I get the following error:
ERROR: fmgr_info: function 18308: cache lookup failed
I have no idea what this is, can anyone help me?
Thanks in advance,
Archie
The table info, aggregate function info and SQL statement are below:
// The superior column is used with the function column_to_upper to
// make the disciples upper case.
CREATE TABLE cards_disciplines
( card_id integer NOT NULL, discipline_id integer NOT NULL, superior boolean NOT NULL, separator char(1) NULL, FOREIGN
KEY(card_id) REFERENCES cards, FOREIGN KEY (discipline_id) REFERENCES disciplines
);
CREATE TABLE disciplines
( discipline_id integer DEFAULT NEXTVAL('disciplines_seq'), discipline_abbr char(3) NOT NULL, discipline text NOT NULL,
PRIMARYKEY(discipline_id)
);
CREATE FUNCTION column_to_upper(text, boolean) RETURNS text AS 'DECLARE discipline ALIAS FOR $1; superior
ALIASFOR $2; BEGIN IF superior THEN RETURN upper(discipline); END IF;
RETURN discipline; END;' LANGUAGE 'plpgsql';
CREATE AGGREGATE discipline_string
( SFUNC = discipline_concat, BASETYPE = text, STYPE = text, FINALFUNC = discipline_fix, INITCOND = ''
);
CREATE FUNCTION discipline_concat(text, text) RETURNS text AS 'DECLARE discipline_str text := ''''; add_str
text:= ''''; temp_str text := ''''; start_int integer := 0; end_int integer := 5; BEGIN IF $2 ~
''[A-Z]''THEN discipline_str := $1 || \' \' || $2; ELSE WHILE char_length(substr($1, start_int,
end_int))> 3 LOOP temp_str := substr($1, start_int, end_int);
IF temp_str ~ ''[A-Z]'' AND discipline_str = '''' THEN discipline_str := add_str || $2 || \' \' ||
temp_str; add_str := ''''; ELSE add_str := add_str || temp_str; END IF;
start_int := start_int + 5; END LOOP;
temp_str := substr($1, start_int, 3); discipline_str := discipline_str || add_str || temp_str; END
IF;
RETURN discipline_str; END; ' LANGUAGE 'plpgsql';
CREATE FUNCTION discipline_fix(text) RETURNS text AS 'SELECT substring($1 from 3 for char_length($1)) AS RESULT'
LANGUAGE'sql';
THE SQL STATEMENT THAT FAILS:
SELECT card_id, discipline_string(column_to_upper(d.discipline_abbr,
cd.superior)) AS discipline
FROM cards_disciplines cd INNER JOIN disciplines d USING (discipline_id)
WHERE cd.discipline_id = d.discipline_id
GROUP BY card_id;
