How can I refer to an ANYELEMENT variable in postgresql dynamic SQL?

Cross-posting from stackoverflow in the hope of getting some additional
eyes on the question.

http://stackoverflow.com/questions/28505782/how-can-i-refer-to-an-anyelement-variable-in-postgresql-dynamic-sql

I'm trying to write a PostgreSQL function for table upserts that can be
used for any table. My starting point is taken from a concrete function for
a specific table type:

CREATE TABLE doodad(id BIGINT PRIMARY KEY, data JSON);
CREATE OR REPLACE FUNCTION upsert_doodad(d doodad) RETURNS VOID AS
$BODY$
BEGIN
LOOP
UPDATE doodad
SET id = (d).id, data = (d).data
WHERE id = (d).id;
IF found THEN
RETURN;
END IF;

-- does not exist, or was just deleted.

BEGIN
INSERT INTO doodad SELECT d.*;
RETURN;
EXCEPTION when UNIQUE_VIOLATION THEN
-- do nothing, and loop to try the update again
END;

END LOOP;
END;
$BODY$
LANGUAGE plpgsql;

The dynamic SQL version of this for any table that I've come up with is
here: SQL Fiddle

CREATE OR REPLACE FUNCTION upsert(target ANYELEMENT) RETURNS VOID AS
$
DECLARE
attr_name NAME;
col TEXT;
selectors TEXT[];
setters TEXT[];
update_stmt TEXT;
insert_stmt TEXT;
BEGIN
FOR attr_name IN SELECT a.attname
FROM pg_index i
JOIN pg_attribute a ON a.attrelid = i.indrelid
AND a.attnum = ANY(i.indkey)
WHERE i.indrelid = format_type(pg_typeof(target),
NULL)::regclass
AND i.indisprimary
LOOP
selectors := array_append(selectors, format('%1$s = target.%1$s',
attr_name));
END LOOP;

FOR col IN SELECT json_object_keys(row_to_json(target))
LOOP
setters := array_append(setters, format('%1$s = (target).%1$s', col));
END LOOP;

update_stmt := format(
'UPDATE %s SET %s WHERE %s',
pg_typeof(target),
array_to_string(setters, ', '),
array_to_string(selectors, ' AND ')
);
insert_stmt := format('INSERT INTO %s SELECT (target).*',
pg_typeof(target));

LOOP
EXECUTE update_stmt;
IF found THEN
RETURN;
END IF;

BEGIN
EXECUTE insert_stmt;
RETURN;
EXCEPTION when UNIQUE_VIOLATION THEN
-- do nothing
END;
END LOOP;
END;
$
LANGUAGE plpgsql;

When I attempt to use this function, I get an error:

SELECT * FROM upsert(ROW(1,'{}')::doodad);

ERROR: column "target" does not exist: SELECT * FROM
upsert(ROW(1,'{}')::doodad)

I tried changing the upsert statement to use placeholders, but I can't
figure out how to invoke it using the record:

EXECUTE update_stmt USING target;

ERROR: there is no parameter $2: SELECT * FROM upsert(ROW(1,'{}')::doodad)

EXECUTE update_stmt USING target.*;

ERROR: query "SELECT target.*" returned 2 columns: SELECT * FROM
upsert(ROW(1,'{}')::doodad)

I feel really close to a solution, but I can't figure out the syntax issues.

--

Christopher Currie

Engineering, Usermind

codemonkey(at)usermind(dot)com

206.353.2867 x109