cancel
Showing results for 
Search instead for 
Did you mean: 

At the time of package compilation getting an error Invalid type name “tablename%rowtype”.

Symptoms

 

Getting an error Invalid type name “tablename%rowtype” when creating a function or procedure.

 

Solution

A variable of a composite type is called a row variable (or row-type variable). Such a variable can hold a whole row of a SELECT or FOR query result, so as long as that query's column set matches the declared type of the variable.

 

A row variable can be declared to have the same type as the rows of an existing table or view by using the table_name%ROWTYPE notation or it can be declared by giving a composite type name. 

 

Sometimes we can get an error Invalid type name “tablename%rowtype” even if we used some unreserved word in the function. In the below example we have used the function name as f12 and table name as a function.

 

In this case, use double quotes around the table name in the table_name%ROWTYPE notation for the successful compilation of package which contains a function which is holding the row variable.

 

In the below test case function name is f12 and the table name is the "function":

 

edb=# CREATE OR REPLACE PACKAGE pck_new as
function f12() returns text;
END pck_new;
CREATE PACKAGE

 

 

[enterprisedb@localhost bin]# cat /tmp/new.sql

CREATE PACKAGE BODY pck_new is
  function f12() return text as 
  declare
    n function%rowtype;
    t text;
    c cursor for select * from function;
  begin
    t := '''';
    open c;
    loop
      fetch c into n;
      exit when not found;
    end loop;
  return t;
end;
END pck_new;

 

 

edb=# \i /tmp/new.sql
psql.bin:/tmp/new.sql:16: ERROR:  invalid type name "function%rowtype" LINE 4:  n function%rowtype;            ^

 

[enterprisedb@localhost bin]$ cat /tmp/new.sql
CREATE PACKAGE BODY pck_new is function f12() return text as  declare n "function"%rowtype; t text; c cursor for select * from function; begin t := ''''; open c; loop fetch c into n; exit when not found; end loop; return t; end; END pck_new;
edb=# \i /tmp/new.sql
CREATE PACKAGE BODY

 

 The package is compiled successfully and we are not getting ERROR: invalid type name "function%rowtype".

 

Version history
Revision #:
5 of 5
Last update:
4 weeks ago
Updated by: