Re: Odd Invalid type name error in postgresql 9.1

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