A Query that works standalone but not in a f(x)

Ralph Smith <rsmith@10kinfo.com> writes:

Within the function I have:

for darec in select * from purchbt where addr not like ''%STE%STE%'' and
addr not like ''%STE%STE%STE%'' and (addr similar to
''%STE[A-Z]*[0-9]+'' or addr similar to ''%STE[A-Z]*[0-9]+[A-Z]'' or
addr similar to ''%STE[A-Z]*[0-9]+[A-Z][A-Z]'') loop

and it finds no records.
Outside the function, replace '' with ', I DO get the results I expect.

Maybe "addr" is the name of a local variable inside the function?
Pre-9.0, that sort of ambiguity can bite you pretty easily.

regards, tom lane

Once again, youreka, or EUREKA Tom!

There was no indication that the problem was inside the loop, just the
query wouldn't work.
In fact it ran perfectly fine yesterday!...? (Though I am going to go
back and check it now.)

Thanks. I know better but... You can bet I won't make that one again.

Ralph
========================================

Tom Lane wrote:

Ralph Smith <rsmith@10kinfo.com> writes:

Within the function I have:

for darec in select * from purchbt where addr not like ''%STE%STE%'' and
addr not like ''%STE%STE%STE%'' and (addr similar to
''%STE[A-Z]*[0-9]+'' or addr similar to ''%STE[A-Z]*[0-9]+[A-Z]'' or
addr similar to ''%STE[A-Z]*[0-9]+[A-Z][A-Z]'') loop

and it finds no records.
Outside the function, replace '' with ', I DO get the results I expect.

Maybe "addr" is the name of a local variable inside the function?
Pre-9.0, that sort of ambiguity can bite you pretty easily.

regards, tom lane

--

Ralph
_________________________