Skip to content

Instantly share code, notes, and snippets.

@nikolas
Created September 11, 2012 23:06
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save nikolas/3702870 to your computer and use it in GitHub Desktop.
Save nikolas/3702870 to your computer and use it in GitHub Desktop.
upsert_table_vararg
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment