Null Value Problem

[iaresean]

Hi all;

I have a postgres problem; I am trying to update a row in my table. I have written a function called changeRecord, and I gather the data from a perl script and then execute the following statement:

SELECT * FROM changeRecord('$domainname', '$old_host', '$old_sttl', '$old_type', '$old_mx_prio', '$old_data', '$host', '$sttl', '$type', '$mx_prio', '$data')

Basically this pass's the old as well as the new values to the database instruction it to perform an update. The problem is that either one of the following variables can be NULL:
$old_sttl, $old_mxprio, $sttl, and $mx_prio.

It can be any combination of the above, say for instance all of them have values except for $sttl, or $old_sttl and $sttl are NULL and the rest have values.

I know when inserting a NULL I do the following:
SELECT * FROM changeRecord('$domainname', '$old_host', NULL, '$old_type', NULL, '$old_data', '$host', NULL , '$type', NULL, '$data')

Do I have to write out a whole bunch of if statements containing all the different possibilitys or isn't there a method where I can say if no value NULL, like so:

SELECT * FROM changeRecord('$domainname', '$old_host', '$old_sttl' | NULL, '$old_type', '$old_mx_prio' | NULL, '$old_data', '$host', '$ttl' | NULL, '$type', '$mx_prio' | NULL, '$data')

I tried the following:
if(!$sttl){
$sttl = 'NULL';
}

But then the select statements treats it as a value and not actually NULL.

Any ideas, thoughts, or suggestions are greatly appreciated.

Sean.

 

[ceco]

you can do a bunch of ifs (as you said)
in the changeRecord function
and generate the query on the fly in a text variable, then you execute it

http://www.postgresql.org/docs/7.4/...tements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

you can do it without execute, but then again you have to do a lot of ifs

(why do you pass the old values?)