pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Function problems, cache lookup failed
2+ messages / 2 participants
[nested] [flat]

* Function problems, cache lookup failed
@ 2002-08-01 09:47 Archibald Zimonyi <archie@netg.se>
  2002-08-01 14:01 ` Re: Function problems, cache lookup failed Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Archibald Zimonyi @ 2002-08-01 09:47 UTC (permalink / raw)
  To: pgsql-sql


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,
  PRIMARY KEY(discipline_id)
);


CREATE FUNCTION column_to_upper(text, boolean)
  RETURNS text
  AS
    'DECLARE
      discipline ALIAS FOR $1;
      superior ALIAS FOR $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;





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Function problems, cache lookup failed
  2002-08-01 09:47 Function problems, cache lookup failed Archibald Zimonyi <archie@netg.se>
@ 2002-08-01 14:01 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2002-08-01 14:01 UTC (permalink / raw)
  To: Archibald Zimonyi <archie@netg.se>; +Cc: pgsql-sql

Archibald Zimonyi <archie@netg.se> writes:
> ERROR:  fmgr_info: function 18308: cache lookup failed
> I have no idea what this is, can anyone help me?

The aggregate links to the function by OID.  You deleted the function
and recreated it, which means it's a new function with a new OID and
so the aggregate is broken.

You'll need to drop and recreate the aggregate too.  Next time use
CREATE OR REPLACE FUNCTION so you don't need to do that.  (With
CREATE OR REPLACE, you are just modifying the existing function,
so its OID doesn't change.)

7.3 will prevent this sort of mistake...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2002-08-01 14:01 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-01 09:47 Function problems, cache lookup failed Archibald Zimonyi <archie@netg.se>
2002-08-01 14:01 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox