Re: Is there a way to test for UNASSIGNED in pl/pgsql

On 10/29/2012 05:36 PM, Merlin Moncure wrote:
> On Mon, Oct 29, 2012 at 11:26 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> Hello
>>
>>
>> 2012/10/29 Hannu Krosing <hannu(at)2ndquadrant(dot)com>:
>>> Hi
>>>
>>> Is there a way to test for a variable being unassigned in pl/pgsql ?
>>>
>>> I'm writing an audit trigger where I'd like to save full before and
>>> after images into audit log and I really do not like to do
>>>
>>> IF TG_OP IN ('INSERT', 'UPDATE') ...
>>>
>>> I'd like rather better if i could just write
>>>
>>> IF NEW IS ASSIGNED THEN ...
>>>
>>> or even use it straight
>>>
>>> null_if_unnasigned(NEW)
>>>
>>> overriding
>>>
>>> coalesce(NEW, NULL)
>>>
>>> for the same purpose would also be ok
>>>
>> I don't know about any way, how to do it. It is little bit hard,
>> because it means test of state plpgsql variable (and plpgsql property)
>> from SQL expression, and it is not possible from SQL.
>>
>> we can design some new variant of IF statement, where parameter is
>> test of validity some variable instead SQL expression
>>
>> some like
>>
>> IF DEFINED NEW THEN ...
> how is that different from SQL coalesce()?
It seems that the "variable is unassigned" comes before SQL has a chance
to see it.

Would just converting UNDEFINED to NULL be a very bad idea ?

It happens so in many places in SQL queries?

And then in some this NULL is coverted to FALSE :)

--------------------------
Hannu