Pi

January 15, 2010

Upsert, Merge Postgres

Filed under: Uncategorized — rqmedes @ 2:47 am

Postgres doesn’t have  UPSERT or ON DUPLICATE KEY functionality  so if you want to perform an update  if not exists insert in one statement you need to write a merge function.

The manual provides a  good example.

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

However this can become time consuming to write on large/many  tables.

Here is a function that will generate a merge function skeleton for your tables

First create a text aggregate.

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

And here is the function.

CREATE OR REPLACE FUNCTION admin.table_merge(_name text)
RETURNS text AS
$BODY$
Declare sql text;
Declare fsql text;
Declare isql text;
Declare usql text;
Declare psql text;
     BEGIN
	sql = 'Create or replace function admin.merge_'  || _name || '('; 

	SELECT INTO fsql,usql,isql,psql
	        textcat_all(('_'|| column_name || ' ' || data_type) || ', '),
	        textcat_all((column_name || '= _' || column_name) || ', '),
	        textcat_all((column_name) || ', '),
	        textcat_all(('_' || column_name) || ', ')
	FROM information_schema.columns
	WHERE  table_name = _name;

	sql = sql || substring(fsql, 0, length(fsql)-1) || ') RETURNS VOID AS
	$MERGE$
	BEGIN
	   LOOP
	      UPDATE ' || _name || ' SET ' || substring(usql, 0, length(usql)-1) ||  ' Where id = _id; '
	  || ' IF FOUND THEN
	          RETURN;
	      END IF; '
	  || 'BEGIN
	      INSERT INTO ' || _name || '(' || substring(isql, 0, length(isql)-1) || ') Values
	      (' || substring(psql, 0, length(psql)-1) || ');
	      RETURN;
	      EXCEPTION WHEN unique_violation THEN
	      END;
	   END LOOP;
	END;
	$MERGE$
	LANGUAGE ''plpgsql''
        COST 100;';
	return sql;
     END;
$BODY$
  LANGUAGE 'plpgsql'
  COST 100;

January 4, 2010

.net C#4 Dynamics, cool but slow

Filed under: C#, Programming — rqmedes @ 10:58 am

Recently I have been playing with DynamicObjects one of the new features in C# 4.0.
DynamicObjects once extended allows you to specifying dynamic behavior at run time.


By overriding the  TryGetMember and TrySetMember method you can dynamically add and remove properties.


dynamic person = new DynamicClass;

person.Name = “John Smith”;

person.Phone = “32345690”;

By overriding the TrySetIndex and TryGetIndex you can access properties you don’t know about at compile time.
dynamic person = new DynamicClass;
person[“Name”] = “John Smith”;

A basic implementation.

    
public class DynamicClass : DynamicObject
{
        public Dictionary _dictionary = new Dictionary();

        public override bool TryGetMember(GetMemberBinder binder, out object result)
        {
            return _dictionary.TryGetValue(binder.Name, out result);
        }

        public override bool TrySetMember(SetMemberBinder binder, object value)
        {
            _dictionary[binder.Name] = value;
            return true;
        }

        public override bool TrySetIndex(
            SetIndexBinder binder, object[] indexes, object value)
        {
            if (dictionary.ContainsKey((string)indexes[0]))
                _dictionary[(string)indexes[0]] = value;
            else
                _dictionary.Add((string)indexes[0], value);
            return true;
        }
        public override bool TryGetIndex(
            GetIndexBinder binder, object[] indexes, out object result)
        {
            return _dictionary.TryGetValue((string)indexes[0], out result);
        }
}

I found a nice implementation on David Ebbo’s blog.
He is using dynamic to simplify data access by writing a wrapper for SQL queries.

I particularly like the Yield return within the data connection. I was at first concerned about this leaving open connections but used correctly connections area automatically cleaned up for you, check this out.

It all works well however it runs really slow. I would be wary of using dynamic if performance is a consideration.
I will post benchmarks soon.

Blog at WordPress.com.