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@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
