Ticket #240: pg.sql

CREATE USER mythtv
  WITH PASSWORD 'mythtv'
  NOCREATEDB NOCREATEUSER;

CREATE DATABASE mythconverg
  WITH OWNER = mythtv
       ENCODING = 'UNICODE'
       TABLESPACE = pg_default;

\c mythconverg

CREATE FUNCTION "plpgsql_call_handler" () RETURNS language_handler AS '$libdir/plpgsql' LANGUAGE C;

CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler";

\c mythconverg mythtv


CREATE OR REPLACE FUNCTION drop_table_if_exists(text, bool) RETURNS bool AS '
 DECLARE
 opt text;
 rec record;
 BEGIN
 IF $2 THEN
 opt := '' CASCADE'';
 ELSE
 opt := '''';
 END IF;

  SELECT INTO rec oid FROM pg_class WHERE relname = $1::name;
 
 
  IF FOUND THEN
    EXECUTE ''DROP TABLE '' || $1 || opt;
    RETURN true;
  END IF;

 
  RETURN false;
END;
' LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION int4_to_bool(int4)
  RETURNS bool AS
'select case when $1 = 0 then false else true end'
  LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION int4_to_bool(int4) OWNER TO mythtv;

CREATE OR REPLACE FUNCTION int2_to_bool(int2)
  RETURNS bool AS
'select case when $1 = 0 then false else true end'
  LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION int2_to_bool(int2) OWNER TO mythtv;

CREATE OR REPLACE FUNCTION bool_to_int2(bool)
  RETURNS int2 AS
'select case when $1 then 1::int2 else 0::int2 end'
  LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION bool_to_int2(bool) OWNER TO mythtv;

\c mythconverg postgres

CREATE CAST (int4 AS bool)
  WITH FUNCTION int4_to_bool(int4)
  AS IMPLICIT;

CREATE CAST (int2 AS bool)
  WITH FUNCTION int2_to_bool(int2)
  AS IMPLICIT;

CREATE CAST (bool AS int2)
  WITH FUNCTION bool_to_int2(bool)
  AS IMPLICIT;