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

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

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

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