PostgreSQL plpgsql with subquery and dynamic SQL

Question

I am having trouble with the plpsql below. What I am trying to do is:

I have a table name with people's names
I am trying to write a PostgreSQL function that copies non-null columns from one person to another (as part of a larger merge)
There are a lot of columns in the name table, and other tables where I want to do the same thing.
In order to limit the amount of code that needs to be written, I am trying to iterate through an array and generate dynamic SQL

However I cannot get this to work.

What I have so far is:

CREATE OR REPLACE FUNCTION test(first_id bigint, second_id bigint) RETURNS boolean AS $$
DECLARE

    first_name name%ROWTYPE;
    second_name name%ROWTYPE;

    col_name VARCHAR(100);
    sql_block VARCHAR(500);

BEGIN

    SELECT * INTO first_name FROM name WHERE person_id = first_id;
    SELECT * INTO second_name FROM name WHERE person_id = second_id;

    FOREACH col_name IN ARRAY ARRAY['column1', 'column2', 'column3', 'column4']
    LOOP
        ---- The follow line is not working and keeps giving a syntax error
        EXECUTE 'if (first_name .' || col_name || ' IS NULL and second_name.' ||
                col_name || ' IS NOT NULL) THEN UPDATE name set ' || col_name || 
                ' = second_name.' || col_name || ' where name.id = first_name.id; END IF;';
    END LOOP;

    RETURN TRUE;

END;
$$ LANGUAGE plpgsql;

Ihor Romanchenko · Accepted Answer · 2016-03-28 17:40:06Z

First - you need a query that will update single column in a single table:

UPDATE table_name t1
SET column1 = COALESE(t1.column1, t2.colum1)
FROM table_name t2
WHERE t1.id = first_id
AND t2.id = second_id

This query will update the field for the first table if it is empty.

Next - you can split this query into 3 parts:

UPDATE table_name t1
SET

As a header.

column1 = COALESE(t1.column1, t2.colum1), 
column2 = COALESE(t1.column2, t2.colum2), 
...

As a generated list of columns to update.

FROM table_name t2
WHERE t1.id = ?
AND t2.id = ?

As a footer. You need to replace parameters with placeholders here.

Next - just do something like:

EXECUTE header||columns||footer USING first_id, second_id;

That worked very well. Thank you. My only issue was that I could not get the USING to work but had to string-concatenate the ids into the footer query

Collectives™ on Stack Overflow

PostgreSQL plpgsql with subquery and dynamic SQL

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related