#!/bin/bash


PGLIB=/usr/local/pgsql/lib
INC=/usr/src/postgresql-7.3rc2/src/include
TABLE=/tmp/demotable.tbl
MAPPING=/tmp/mapping.tbl
SQL=/tmp/demo.sql

set -e
[ -f $TABLE ] || {
  perl -e 'for ($i = 0 ; $i < 300000 ; $i++) { printf("%s%d\n",chr(65 + ($i % 4)), $i); }' >$TABLE
}
[ -f $MAPPING ] || {
  for sr in A B C D
  do  
    for i in 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
    do
      echo -e "$sr\tstring$i\t$i"
    done
  done >$MAPPING
}
  

cat >$SQL <<EOF
drop table demotable cascade;
create table demotable ( column1 text );
drop table demorub cascade;
create table demorub (
  srto char,
  titel text,
  rnum integer
);
  
copy demotable from '$TABLE';
copy demorub from '$MAPPING';
create or replace function mapping (char, text) returns int as '
select rnum from demorub where srto = \$1 and titel = \$2
' language 'sql';
select count(mapping(substring(column1,1,1), 'string2')) from demotable;
EOF
psql <$SQL