Here is a handy way of getting the distinct product of one or more arrays in postgres.
SELECT Distinct unnest(array_cat(ARRAY[1,2,3], ARRAY[1,2,4,5]))
anyarray: concatenate two arrays
unnest: expand an array to a set of rows
Results will be
1,2,3,4,5.
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;
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.
I have been using Pg_Unit to create unit tests for my latest postgres database application.
You can get PG_Unit from here http://en.dklab.ru/lib/dklab_pgunit/
Pg_unit is pretty bare bones allowing you to basically assert values are the same and as the the forums are in Russian it is hard to find any further information.
I find it useful to write tests for all my validation logic, checks and constraints. This is useful when you have lots of tables and data validation checks.
A basic example:
insert into inventory(code) values (‘a”);
PERFORM pgunit.assert_fail(‘insert into inventory(code) values (”a”);’);
which inserts a record and then asserts that the same record cannot be inserted again.
As pg_unit runs all the tests within a transaction that is rolled back none of your test code will affect your data.
Here is the function to assert failures.
CREATE OR REPLACE FUNCTION pgunit.assert_fail(sql_statement text)
RETURNS void AS
$BODY$
DECLARE ok boolean;
BEGIN
BEGIN
ok := false;
execute $1;
EXCEPTION
WHEN Others THEN
ok := true;
END;
IF ok is false THEN
PERFORM pgunit.fail('Should have Failed');
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
Because A property, indexer or dynamic member access may not be passed as an out or ref parameter TryParse can be annoying.
Here is the syntax I use to make it simpler.
int i;
Id = int.TryParse(“10″,out i) ? i : 0;
As a follow up on pagination in MSSQL the pagination technique also works in postgres.
Pagination in MSSQL with total number of records.
although it is alot simpler in postgres
SELECT * , count(*) OVER()as total from “Table” limit 20 OFFSET 10
Pagination is easy in most scenarios
You usually handle it in code letting your favourite ORM such as NHibernate take care of it for you.
There are times when you are required to hand-roll your own sql which is as simple as
SELECT * FROM <Table> WHERE <Criteria> LIMIT 0, 10
or for SQL Server
ROW_NUMBER() OVER (ORDER BY <Column>) AS RowNumber
But this doesn’t return the total number of records, important for presenting ‘Page 1 of 380‘.
I assume most would do an additional sql call.
SELECT COUNT(*) FROM <Table> WHERE <Criteria>
In SQL Server it is easy to get both in one query.
WITH Results AS (
SELECT ROW_NUMBER() OVER (ORDER BY <Column>) AS RowNumber,
ROW_NUMBER() OVER (ORDER BY <Column> DESC) AS RowNumberDesc,
* FROM <Table>
WHERE <Criteria>)
SELECT * FROM Results WHERE RowNumber BETWEEN <Start> AND <End>
This will return the results between <Start> and <End> ordered by <Column>.
You can get the Total Number of records by adding the <Start> value to the RowNumberDesc value of the first record returned.
Preliminary benchmarking shows that it performs faster than running the two queries method.
Anonymous classes can be a great way to protect your models from being polluted by any view specific requirements.
Lets say you have a Product model that you want to present as a list in a table.
You now want to add some extra information to present any pre selected items.
By creating an anonymous type you can easily extend your product to add the additional information, Selected.
e.g
var test = new { Product = product, Selected = true};
Combining this with Linq makes it easy to return a list that contains the products and any other information required by the view engine
var selectedId = 3;
var products = (from p in _productRepository.GetAll()
select new { Product = p, Selected = p.Id == selectedId });
This will set selected as true for the product with the Id of 3.
