Thread: Odd Invalid type name error in postgresql 9.1

Hi,

After upgrading from 8.3 I found an unusual error related to a plpgsql function.

The database includes a table named "detail".

The procedure/function in question includes a declaration of detail%rowtype.

Loading the server from a dump-all at the time of the upgrade went
fine and the function was loaded along with all the other database
objects. I noticed later on that at runtime (e.g. calling the
function) I was receiving an "invalid type name" error on the
declaration using "detail%rowtype".

This seemed odd. I couldn't find any record of "detail" being a
reserved word, and the usual sql queries using the table perform
without a hitch. But when I changed the declaration in the function to
have quotes around the table name (e.g. "detail"%rowtype) the error
went away.

Any ideas on this? My biggest concern is if there is a structural
problem in the database that might result in a crash later, I'd like
to get it fixed now.

Thanks in advance,

Jim Wilson

On 09/26/2012 12:55 PM, Jim Wilson wrote:
> Hi,
>
> After upgrading from 8.3 I found an unusual error related to a plpgsql function.
>
> The database includes a table named "detail".
>
> The procedure/function in question includes a declaration of detail%rowtype.
>
> Loading the server from a dump-all at the time of the upgrade went
> fine and the function was loaded along with all the other database
> objects. I noticed later on that at runtime (e.g. calling the
> function) I was receiving an "invalid type name" error on the
> declaration using "detail%rowtype".
>
> This seemed odd. I couldn't find any record of "detail" being a
> reserved word, and the usual sql queries using the table perform
> without a hitch. But when I changed the declaration in the function to
> have quotes around the table name (e.g. "detail"%rowtype) the error
> went away.
>
> Any ideas on this? My biggest concern is if there is a structural
> problem in the database that might result in a crash later, I'd like
> to get it fixed now.

My hunch is it is related to the below, just not sure how. Greater minds
will need to confirm or deny:)

http://www.postgresql.org/docs/9.1/interactive/release-9-1.html
"Type modifiers of PL/pgSQL variables are now visible to the SQL parser
(Tom Lane)

A type modifier (such as a varchar length limit) attached to a PL/pgSQL
variable was formerly enforced during assignments, but was ignored for
all other purposes. Such variables will now behave more like table
columns declared with the same modifier. This is not expected to make
any visible difference in most cases, but it could result in subtle
changes for some SQL commands issued by PL/pgSQL functions."

>
> Thanks in advance,
>
> Jim Wilson
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

Jim Wilson <jimw@kelcomaine.com> writes:
> After upgrading from 8.3 I found an unusual error related to a plpgsql function.

> The database includes a table named "detail".

> The procedure/function in question includes a declaration of detail%rowtype.

Hmm.  The reason that doesn't work is that DETAIL is now a keyword in
plpgsql.  It's supposed to be an unreserved keyword, so ideally this
usage would work; but I see that plpgsql's read_datatype() function
is naive about such cases (in particular, it fails to check for the
%rowtype construct).  I'll see about fixing it, but in the meantime
your best answer is to quote the name.

            regards, tom lane