Re: Cannot execute null query - answer

From: "Rison, Stuart" <srison(at)rvc(dot)ac(dot)uk>
To: "'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org>
Cc: "'ffabrizio(at)mmrd(dot)com'" <ffabrizio(at)mmrd(dot)com>, "'tgl(at)sss(dot)pgh(dot)pa(dot)us'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Cannot execute null query - answer
Date: 2002-11-27 12:41:17
Message-ID: 6BD8CE460CC6EE40B83DDFCED609F84BE140DF@cmnt4008.rvc.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)mmrd(dot)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

Browse pgsql-general by date

  From Date Subject
Next Message CN 2002-11-27 12:54:06 Server v7.3RC2 Dies
Previous Message Bruno Wolff III 2002-11-27 12:24:48 Re: Rép