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
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 |