Thread: Is there a way to test for UNASSIGNED in pl/pgsql
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
---------------------
Hannu
Hello
2012/10/29 Hannu Krosing <hannu@2ndquadrant.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 ...
Regards
Pavel Stehule
> ---------------------
> Hannu
>
On Mon, Oct 29, 2012 at 11:26 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Hello
>
>
> 2012/10/29 Hannu Krosing <hannu@2ndquadrant.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()?
merlin
On 10/29/2012 05:36 PM, Merlin Moncure wrote:
> On Mon, Oct 29, 2012 at 11:26 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> Hello
>>
>>
>> 2012/10/29 Hannu Krosing <hannu@2ndquadrant.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
Hannu Krosing <hannu@2ndQuadrant.com> writes:
> Would just converting UNDEFINED to NULL be a very bad idea ?
Yeah, it would, at least for the normal case where an undefined record
variable has no well-defined type either. Saying that it's NULL doesn't
provide an answer to what type it has.
In the case of OLD/NEW we do know that they ought to have the rowtype of
the underlying table, so it's conceivable that we could pretend they are
nulls of that type. But then you get into the question of whether a
NULL record is distinguishable from (NULL, NULL, ...) --- which would be
a perfectly legal real value of these records. Note that the SQL
standard is pretty firmly on the side that says they are not
distinguishable. I don't agree with that personally, but I don't think
it'd be a good idea to make a fundamental plpgsql feature depend on
being able to distinguish these states.
TBH, I think your request is ill-considered and should be rejected.
There is nothing whatsoever wrong with testing TG_OP to see what sort of
trigger you're in.
regards, tom lane
2012/10/29 Hannu Krosing <hannu@2ndquadrant.com>:
> On 10/29/2012 05:36 PM, Merlin Moncure wrote:
>>
>> On Mon, Oct 29, 2012 at 11:26 AM, Pavel Stehule <pavel.stehule@gmail.com>
>> wrote:
>>>
>>> Hello
>>>
>>>
>>> 2012/10/29 Hannu Krosing <hannu@2ndquadrant.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 ?
I don't like it
>
> It happens so in many places in SQL queries?
>
> And then in some this NULL is coverted to FALSE :)
>
> --------------------------
> Hannu
>
>
>
>
>
>
>
>
2012/10/29 Tom Lane <tgl@sss.pgh.pa.us>: > Hannu Krosing <hannu@2ndQuadrant.com> writes: >> Would just converting UNDEFINED to NULL be a very bad idea ? > > Yeah, it would, at least for the normal case where an undefined record > variable has no well-defined type either. Saying that it's NULL doesn't > provide an answer to what type it has. > > In the case of OLD/NEW we do know that they ought to have the rowtype of > the underlying table, so it's conceivable that we could pretend they are > nulls of that type. But then you get into the question of whether a > NULL record is distinguishable from (NULL, NULL, ...) --- which would be > a perfectly legal real value of these records. Note that the SQL > standard is pretty firmly on the side that says they are not > distinguishable. I don't agree with that personally, but I don't think > it'd be a good idea to make a fundamental plpgsql feature depend on > being able to distinguish these states. > > TBH, I think your request is ill-considered and should be rejected. > There is nothing whatsoever wrong with testing TG_OP to see what sort of > trigger you're in. +1 we can define two vars TG_NEW_IS_VALID and TG_OLD_IS_VALID. and then you can write test IF TG_NEW_IS_VALID THEN -- do some with NEW ELSE -- ... END IF; Regards Pavel > > regards, tom lane