Thread: Function created yet not found

Using PostgreSQL 7.1.3....

I start off without the function....

test=# select findparenttype(10056,'S');
ERROR:  Function 'findparenttype(int4, unknown)' does not exist
         Unable to identify a function that satisfies the given argument
types
         You may need to add explicit typecasts

So I create the function....

test=# create function findparenttype(int4, varchar) returns int4 as '
test'# select parent.entity_id from entity parent, entity child where
child.entity_id = $1 and child.lft between parent.lft and parent.rgt and
parent.type = $2;
test'# ' language 'sql';
CREATE

And then I try to use it:

test=# select findparenttype(10056,'S');
ERROR:  Function OID 14997123 does not exist
test=#

I've never seen that happen before, what causes something like this?  It
recognized that there was supposed to be a function with those
parameters, yet couldn't find it though I'd just created it.  Nothing in
the log indicates anything unusual to me.  I usually see this error when
the function I'm calling is referencing another function which has since
been dropped or reloaded, but you can see findparenttype doesn't
reference any other functions of mine.

I've been creating and dropping this function from the same script all
day without issues until now.

Thanks,
Fran

Call off the dogs. =)

I dug deeper, and found that it wasn't the function that was throwing
the error, but the table it was selecting from.  Examining the table
further....

test=# \d entity
               Table "entity"
  Attribute |       Type        | Modifier
-----------+-------------------+----------
  entity_id | integer           |
  name      | character varying |
  type      | character varying |
  active    | boolean           |
  lft       | integer           |
  rgt       | integer           |
Indices: entity_findsite_idx,
          entity_lft_rgt_idx,
          entity_rgt_idx

entity_findsite_idx looked odd to me, not my usual naming scheme.
Co-worker had tested making an index using a function: 'create index
entity_findsite_idx on entity (findsite(entity_id))'.  I had since
dropped and reloaded that function, rendering the table useless.

My apologies for prematurely posting.  I've never used function-based
indices....would a select findsite(entity_id) from entity actually have
used that index?  That could be handy.

Thanks,
Fran