regexp err msg question

"Gauthier, Dave" <dave.gauthier@intel.com> writes:

mydb=# select distinct fivr from alphaview where name ~ ''^foo'';
ERROR: type "foo" does not exist

(note: those are two single quotes before the ^ and after foo, NOT double quotes)

You realize of course that you've got too many quotes there.

Could someone explain the error message?

I think it's parsing that as

name ~ '' ^ foo ''

That is, empty string literal, ^ operator, typename preceding string
literal, empty string literal. The "typename 'string'" syntax wasn't
one of the SQL committee's better ideas :-(

regards, tom lane

Yes, someone is using too many quotes, I think in a script because the log file
gets bursts of these messages. I need to foind out who's doing this. I know
pg_stat_activity is the key in that endevour, but these are remote db queries,
so I have to figure out how to deal with that indirection.

I was curious where the reference to "type" came from in the error message.

Thanks Tom.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, December 07, 2010 10:45 AM
To: Gauthier, Dave
Cc: pgsql-general List
Subject: Re: [GENERAL] regexp err msg question

"Gauthier, Dave" <dave.gauthier@intel.com> writes:

mydb=# select distinct fivr from alphaview where name ~ ''^foo'';
ERROR: type "foo" does not exist

(note: those are two single quotes before the ^ and after foo, NOT double quotes)

You realize of course that you've got too many quotes there.

Could someone explain the error message?

I think it's parsing that as

name ~ '' ^ foo ''

That is, empty string literal, ^ operator, typename preceding string
literal, empty string literal. The "typename 'string'" syntax wasn't
one of the SQL committee's better ideas :-(

regards, tom lane