Re: Cannot execute null query - answer
OK, this has got to be one of the longest delays between a query and an
answer (7 months), and my guess is you've sorted it out by now or found a
solution... but since I didn't see an answer posted.
I had the same problem and I found the solution. Basically it happens when
you use the concatenation operator (||) and one of you element is NULL. It
doesn't matter which element is NULL, the whole concat chain will evaluate
to NULL. So...
EXECUTE ''SELECT ''Hello'' ||
quote_literal(a_value_which_happens_to_be_NULL) || ''World'''';
will fail because it turns into
EXECUTE NULL (hence the reported error message).
My solution:
EXECUTE ''SELECT ''Hello'' ||
COALESCE(quote_literal(a_value_which_happens_to_be_NULL), ''NULL'' ||
''World'''';
which will work because it turns into
EXECUTE SELECT 'Hello' || NULL || 'World'; (this is of course a bogus
example, but you catch my drift, the idea is to get the STRING 'NULL' in the
execute statement, rather than the VALUE NULL.)
HTH, even after all this time ;)
S.
Show quoted text
Fran Fabrizio <ffabrizio@mmrd.com> writes:
I got this error when trying to use a view, so I suspect that it was the
view definition query that was throwing this. I'd never seen this error
before so I did a search of the list archives and the newsgroups and web
in general and found nothing. From the pattern of it happening, my best
guess is that the underlying table had some data in it that was busting
the view query, but having never seen this before I don't even know
where to start looking.The only occurrences of that string that I can find in the source code
are in plpgsql: the various forms of EXECUTE throw that error if the
expression that's supposed to yield a query string yields NULL instead.
However, if that's what was happening then you should have seen some
indicator that the error was in a plpgsql function, not just the bare
error message.regards, tom lane