Pi

February 9, 2010

Joining Arrays in Postgres Sql

Filed under: Uncategorized — rqmedes @ 4:51 am

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.

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;

November 14, 2009

Unit Testing Postgres

Filed under: Uncategorized — rqmedes @ 11:55 am

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;

November 8, 2009

TryParse Shorthand

Filed under: Uncategorized — rqmedes @ 4:48 am

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;

October 29, 2009

Postgres Paginations

Filed under: Uncategorized — rqmedes @ 6:07 am

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

Create a free website or blog at WordPress.com.