For instance I've got a stored procedure to import data from csv files and write the read data into a SQL table. I've got a table defined as below:
CREATE TABLE person (id int, name text, age int, married boolean);
First I check if the record exist already, if exists I do update, if doesn't - insert. Each field of record may have a different type, so the result of a SQL command are assigned to a list of scalar variables:
SELECT name, age, married INTO v_name, v_age, v_married [..]
Let's assume every column is declared as optional (NULL allowed). What's the best way then to check which variable (v_name, v_age, v_married) is not NULL and can be processed?
I've found many solutions:
- IF NOT FOUND THEN
- WHEN NO_DATA_FOUND THEN
- IF v_age IS NOT NULL THEN [...]
or dynamic solution I'm using now using the last way I've mentioned above, when I have to check multiple columns (col):
list_of_columns := ARRAY['name','age','married'];
FOREACH x IN ARRAY list_of_columns LOOP
EXECUTE 'SELECT ' || x
|| ' FROM person
WHERE id = ' || quote_literal(v_id)
INTO y;
IF x = 'name' AND (y != v_name OR y IS NULL) THEN
UPDATE person
SET name = v_name
WHERE id = v_id;
ELSIF x = 'age' AND (y != v_age OR y IS NULL) THEN
UPDATE person
SET age = v_age
WHERE id = v_id;
ELSIF x = 'married' AND (y != v_married OR y IS NULL) THEN
UPDATE person
SET married= v_married
WHERE id = v_id;
END IF;
END LOOP;
I'm looking for the best solutions having regard to the best practice and performance. Any help is appreciated!