upsert_table_vararg

upsert_table_vararg.sql

-- This function is a generalized way to upsert multiple optional fields of a
-- table based on the hstore param "my_params"
create or replace function upsert_table_vararg(
	my_table_name text,
	my_id_col_name text,
	my_params hstore
)
	returns void as $$
declare vkey text;
declare vquery text;
begin
	-- If we didn't pass in an id, set it to 0
	if not my_params ? my_id_col_name then
		my_params := my_params || hstore(my_id_col_name, '0');
	end if;

	vquery := 'update ' || quote_ident(my_table_name) || ' set ';
	-- Loop through each param, editing the query string
	foreach vkey in array akeys(my_params) loop
		-- Don't let users overwrite the id
		if vkey != my_id_col_name then
			-- Ignore keys that have a NULL value 
			if defined(my_params, vkey) then
				vquery := vquery
					|| quote_ident(vkey) || ' = '
					|| quote_literal(my_params -> vkey) || ', ';
			end if;
		end if;
	end loop;

	-- Remove trailing comma
	vquery := trim(trailing ', ' from vquery);

	vquery := vquery
		|| ' where ' || quote_ident(my_id_col_name) || ' = '
		|| quote_literal(my_params -> my_id_col_name);

	execute vquery;

	-- If a row doesn't exist, the update will fail and this insert
	-- will succeed

	-- Build an insert query that looks like this:
	--
	-- insert into nexus_device_status (status)
	-- select 'BUSY'
	-- where not exists (
	--   select 1
	--   from nexus_device_status 
	--   where device_id = '0'
	-- )
	--
	vquery := 'insert into ' || quote_ident(my_table_name) || ' (';
	-- Loop through each param, editing the query string
	foreach vkey in array akeys(my_params) loop
		-- Ignore keys that have a NULL value 
		if defined(my_params, vkey) then
			vquery := vquery || quote_ident(vkey) || ',';
		end if;
	end loop;
	-- Remove trailing comma
	vquery := trim(trailing ', ' from vquery);

	vquery := vquery || ') select ';

	foreach vkey in array akeys(my_params) loop
		-- Ignore keys that have a NULL value 
		if defined(my_params, vkey) then
			vquery := vquery || quote_literal(my_params -> vkey) || ', ';
		end if;
	end loop;
	-- Remove trailing comma
	vquery := trim(trailing ', ' from vquery);

	vquery := vquery ||
		' where not exists (
			select 1
			from ' || quote_ident(my_table_name) || ' 
			where ' || quote_ident(my_id_col_name) ||
			' = ' || quote_literal(my_params -> my_id_col_name)
		|| ')';

	execute vquery;
end; $$ language plpgsql;