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.

>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