HsSqlPpp-0.4.0



The type checking examples have the sql on the left and the result of type checking on the right. Different sections are using different tests:

It's a bit rough at the moment, the clarity will be improved.

The source this file is generated from is here: TypeCheckTests.lhs

typeCheckTests

basic literal types

1
 
Right (ScalarType "int4")
1.0
 
Right (ScalarType "numeric")
'test'
 
Right UnknownType
true
 
Right (ScalarType "bool")
array[1,2,3]
 
Right (ArrayType (ScalarType "int4"))
array['a','b']
 
Right (ArrayType UnknownType)
array['a'::text,'b']
 
Right (ArrayType (ScalarType "text"))
array['a','b'::text]
 
Right (ArrayType (ScalarType "text"))
array[1,'b']
 
Right (ArrayType (ScalarType "int4"))
array[1,true]
 
Left
  [NoMatchingOperator "!arrayctor"
     [ScalarType "int4", ScalarType "bool"]]

simple expressions

1=1
 
Right (ScalarType "bool")
1=true
 
Left
  [NoMatchingOperator "=" [ScalarType "int4", ScalarType "bool"]]
substring('aqbc' from 2 for 2)
 
Right (ScalarType "text")
substring(3 from 2 for 2)
 
Left
  [NoMatchingOperator "!substring"
     [ScalarType "int4", ScalarType "int4", ScalarType "int4"]]
substring('aqbc' from 2 for true)
 
Left
  [NoMatchingOperator "!substring"
     [UnknownType, ScalarType "int4", ScalarType "bool"]]
3 between 2 and 4
 
Right (ScalarType "bool")
3 between true and 4
 
Left
  [IncompatibleTypeSet
     [ScalarType "int4", ScalarType "bool", ScalarType "int4"]]
array[1,2,3][2]
 
Right (ScalarType "int4")
array['a','b'][1]
 
Right UnknownType
array['a'::text,'b'][1]
 
Right (ScalarType "text")
not true
 
Right (ScalarType "bool")
not 1
 
Left [NoMatchingOperator "!not" [ScalarType "int4"]]
@ 3
 
Right (ScalarType "int4")
@ true
 
Left [NoMatchingOperator "@" [ScalarType "bool"]]
-3
 
Right (ScalarType "int4")
-'a'
 
Left [NoMatchingOperator "-" [UnknownType]]
4-3
 
Right (ScalarType "int4")
1 is null
 
Right (ScalarType "bool")
1 is not null
 
Right (ScalarType "bool")
1+1
 
Right (ScalarType "int4")
1+1
 
Right (ScalarType "int4")
31*511
 
Right (ScalarType "int4")
5/2
 
Right (ScalarType "int4")
2^10
 
Right (ScalarType "float8")
17%5
 
Right (ScalarType "int4")
3 and 4
 
Left
  [NoMatchingOperator "!and" [ScalarType "int4", ScalarType "int4"]]
True and False
 
Right (ScalarType "bool")
false or true
 
Right (ScalarType "bool")
lower('TEST')
 
Right (ScalarType "text")
lower(1)
 
Left [NoMatchingOperator "lower" [ScalarType "int4"]]

special functions

coalesce(null,1,2,null)
 
Right (ScalarType "int4")
coalesce('3',1,2,null)
 
Right (ScalarType "int4")
coalesce('3',1,true,null)
 
Left
  [NoMatchingOperator "coalesce"
     [UnknownType, ScalarType "int4", ScalarType "bool", UnknownType]]
nullif('hello','hello')
 
Right UnknownType
nullif('hello'::text,'hello')
 
Right (ScalarType "text")
nullif(3,4)
 
Right (ScalarType "int4")
nullif(true,3)
 
Left
  [NoMatchingOperator "nullif"
     [ScalarType "bool", ScalarType "int4"]]
greatest(3,5,6,4,3)
 
Right (ScalarType "int4")
least(3,5,6,4,3)
 
Right (ScalarType "int4")
least(5,true)
 
Left
  [NoMatchingOperator "least" [ScalarType "int4", ScalarType "bool"]]

row comparison expressions

row(1)
 
Right (AnonymousRecordType [ScalarType "int4"])
row(1,2)
 
Right (AnonymousRecordType [ScalarType "int4", ScalarType "int4"])
row('t1','t2')
 
Right (AnonymousRecordType [UnknownType, UnknownType])
row(true,1,'t3')
 
Right
  (AnonymousRecordType
     [ScalarType "bool", ScalarType "int4", UnknownType])
(true,1,'t3',75.3)
 
Right
  (AnonymousRecordType
     [ScalarType "bool", ScalarType "int4", UnknownType,
      ScalarType "numeric"])
row(1) = row(2)
 
Right (ScalarType "bool")
row(1,2) = row(2,1)
 
Right (ScalarType "bool")
(1,2) = (2,1)
 
Right (ScalarType "bool")
(1,2,3) = (2,1)
 
Left
  [NoMatchingOperator "="
     [AnonymousRecordType
        [ScalarType "int4", ScalarType "int4", ScalarType "int4"],
      AnonymousRecordType [ScalarType "int4", ScalarType "int4"]]]
('1',2) = (2,'1')
 
Right (ScalarType "bool")
(1,true) = (2,1)
 
Left
  [NoMatchingOperator "="
     [AnonymousRecordType [ScalarType "int4", ScalarType "bool"],
      AnonymousRecordType [ScalarType "int4", ScalarType "int4"]]]
(1,2) <> (2,1)
 
Right (ScalarType "bool")
(1,2) >= (2,1)
 
Right (ScalarType "bool")
(1,2) <= (2,1)
 
Right (ScalarType "bool")
(1,2) > (2,1)
 
Right (ScalarType "bool")
(1,2) < (2,1)
 
Right (ScalarType "bool")

case expressions

case
 when true then 1
end
 
Right (ScalarType "int4")
case
 when 1=2 then 'stuff'
 when 2=3 then 'blah'
 else 'test'
end
 
Right UnknownType
case
 when 1=2 then 'stuff'
 when 2=3 then 'blah'
 else 'test'::text
end
 
Right (ScalarType "text")
case
 when 1=2 then 'stuff'
 when true=3 then 'blah'
 else 'test'
end
 
Left
  [NoMatchingOperator "=" [ScalarType "bool", ScalarType "int4"]]
case
 when 1=2 then true
 when 2=3 then false
 else 1
end
 
Left
  [IncompatibleTypeSet
     [ScalarType "bool", ScalarType "bool", ScalarType "int4"]]
case
 when 1=2 then false
 when 2=3 then 1
 else true
end
 
Left
  [IncompatibleTypeSet
     [ScalarType "bool", ScalarType "int4", ScalarType "bool"]]
case 1 when 2 then 3 else 4 end
 
Right (ScalarType "int4")
case 1 when true then 3 else 4 end
 
Left [IncompatibleTypeSet [ScalarType "int4", ScalarType "bool"]]
case 1 when 2 then true else false end
 
Right (ScalarType "bool")
case 1 when 2 then 3 else false end
 
Left [IncompatibleTypeSet [ScalarType "int4", ScalarType "bool"]]

misc expressions

some expressions

3 + '4'
 
Right (ScalarType "int4")
3.0 + '4'
 
Right (ScalarType "numeric")
'3' + '4'
 
Left [NoMatchingOperator "+" [UnknownType, UnknownType]]

exists expressions

exists (select 1 from pg_type)
 
Right (ScalarType "bool")
exists (select testit from pg_type)
 
Left [UnrecognisedIdentifier "testit"]

polymorphic functions

array_append(ARRAY[1,2], 3)
 
Right (ArrayType (ScalarType "int4"))
array_append(ARRAY['a','b'], 'c')
 
Right (ArrayType UnknownType)
array_append(ARRAY['a','b'], 'c'::text)
 
Right (ArrayType (ScalarType "text"))
array_append(ARRAY['a','b'::text], 'c')
 
Right (ArrayType (ScalarType "text"))
array_append(ARRAY['a'::int,'b'], 'c')
 
Right (ArrayType (ScalarType "int4"))

cast expressions

cast ('1' as integer)
 
Right (ScalarType "int4")
cast ('1' as baz)
 
Left [UnknownTypeName "baz"]
array[]
 
Right (Pseudo AnyArray)
array[] :: text[]
 
Right (ArrayType (ScalarType "text"))

simple selects

select 1;
 
Right [Just ([], [("?column?", ScalarType "int4")])]
select 1 as a;
 
Right [Just ([], [("a", ScalarType "int4")])]
select 1,2;
 
Right
  [Just
     ([],
      [("?column?", ScalarType "int4"),
       ("?column?", ScalarType "int4")])]
select 1 as a, 2 as b;
 
Right
  [Just ([], [("a", ScalarType "int4"), ("b", ScalarType "int4")])]
select 1+2 as a, 'a' || 'b';
 
Right
  [Just
     ([], [("a", ScalarType "int4"), ("?column?", ScalarType "text")])]
values (1,2);
 
Right
  [Just
     ([],
      [("column1", ScalarType "int4"), ("column2", ScalarType "int4")])]
values (1,2),('3', '4');
 
Right
  [Just
     ([],
      [("column1", ScalarType "int4"), ("column2", ScalarType "int4")])]
values (1,2),('a', true);
 
Left [IncompatibleTypeSet [ScalarType "int4", ScalarType "bool"]]
values ('3', '4'),(1,2);
 
Right
  [Just
     ([],
      [("column1", ScalarType "int4"), ("column2", ScalarType "int4")])]
values ('a', true),(1,2);
 
Left [IncompatibleTypeSet [ScalarType "bool", ScalarType "int4"]]
values ('a'::text, '2'::int2),('1','2');
 
Right
  [Just
     ([],
      [("column1", ScalarType "text"), ("column2", ScalarType "int2")])]
values (1,2,3),(1,2);
 
Left [ValuesListsMustBeSameLength]

combine selects

select 1,2  union select '3', '4';
 
Right
  [Just
     ([],
      [("?column?", ScalarType "int4"),
       ("?column?", ScalarType "int4")])]
select 1,2 intersect select 'a', true;
 
Left [IncompatibleTypeSet [ScalarType "int4", ScalarType "bool"]]
select '3', '4' except select 1,2;
 
Right
  [Just
     ([],
      [("?column?", ScalarType "int4"),
       ("?column?", ScalarType "int4")])]
select 'a', true union select 1,2;
 
Left [IncompatibleTypeSet [ScalarType "bool", ScalarType "int4"]]
select 'a'::text, '2'::int2 intersect select '1','2';
 
Right
  [Just
     ([], [("text", ScalarType "text"), ("int2", ScalarType "int2")])]
select 1,2,3 except select 1,2;
 
Left [ValuesListsMustBeSameLength]
select '3' as a, '4' as b except select 1,2;
 
Right
  [Just ([], [("a", ScalarType "int4"), ("b", ScalarType "int4")])]

selects from

simple selects from

select a from (select 1 as a, 2 as b) x;
 
Right [Just ([], [("a", ScalarType "int4")])]
select b from (select 1 as a, 2 as b) x;
 
Right [Just ([], [("b", ScalarType "int4")])]
select c from (select 1 as a, 2 as b) x;
 
Left [UnrecognisedIdentifier "c"]
select typlen from pg_type;
 
Right [Just ([], [("typlen", ScalarType "int2")])]
select oid from pg_type;
 
Right [Just ([], [("oid", ScalarType "oid")])]
select p.oid from pg_type p;
 
Right [Just ([], [("oid", ScalarType "oid")])]
select typlen from nope;
 
Left [UnrecognisedRelation "nope"]
select generate_series from generate_series(1,7);
 
Right [Just ([], [("generate_series", ScalarType "int4")])]
select generate_series.generate_series from generate_series(1,7);
 
Right [Just ([], [("generate_series", ScalarType "int4")])]
select g from generate_series(1,7) g;
 
Right [Just ([], [("g", ScalarType "int4")])]
select g.g from generate_series(1,7) g;
 
Right [Just ([], [("g", ScalarType "int4")])]
select generate_series.g from generate_series(1,7) g;
 
Left [UnrecognisedCorrelationName "generate_series"]
select g.generate_series from generate_series(1,7) g;
 
Left [UnrecognisedIdentifier "generate_series"]
select * from pg_attrdef;
 
Right
  [Just
     ([],
      [("adrelid", ScalarType "oid"), ("adnum", ScalarType "int2"),
       ("adbin", ScalarType "pg_node_tree"),
       ("adsrc", ScalarType "text")])]
select abs from abs(3);
 
Right [Just ([], [("abs", ScalarType "int4")])]

simple selects from 2

[CatCreateFunction FunName "testfunc" [] (Pseudo Void) False]
select testfunc();
 
Right [Just ([], [])]

simple join selects

select * from (select 1 as a, 2 as b) a
  cross join (select true as c, 4.5 as d) b;
 
Right
  [Just
     ([],
      [("a", ScalarType "int4"), ("b", ScalarType "int4"),
       ("c", ScalarType "bool"), ("d", ScalarType "numeric")])]
select * from (select 1 as a, 2 as b) a
  inner join (select true as c, 4.5 as d) b on true;
 
Right
  [Just
     ([],
      [("a", ScalarType "int4"), ("b", ScalarType "int4"),
       ("c", ScalarType "bool"), ("d", ScalarType "numeric")])]
select * from (select 1 as a, 2 as b) a
  inner join (select 1 as a, 4.5 as d) b using(a);
 
Right
  [Just
     ([],
      [("a", ScalarType "int4"), ("b", ScalarType "int4"),
       ("d", ScalarType "numeric")])]
select * from (select 1 as a, 2 as b) a
 natural inner join (select 1 as a, 4.5 as d) b;
 
Right
  [Just
     ([],
      [("a", ScalarType "int4"), ("b", ScalarType "int4"),
       ("d", ScalarType "numeric")])]
select * from (select 2 as b, 1 as a) a
 natural inner join (select 4.5 as d, 1 as a) b;
 
Right
  [Just
     ([],
      [("a", ScalarType "int4"), ("b", ScalarType "int4"),
       ("d", ScalarType "numeric")])]
select * from (select 1 as a1) a, (select 2 as a2) b;
 
Right
  [Just ([], [("a1", ScalarType "int4"), ("a2", ScalarType "int4")])]
select * from (select 1 as a1) a, (select 2 as a1) b;
 
Right
  [Just ([], [("a1", ScalarType "int4"), ("a1", ScalarType "int4")])]

simple scalar identifier qualification

select a.* from 
(select 1 as a, 2 as b) a 
cross join (select 3 as c, 4 as d) b;
 
Right
  [Just ([], [("a", ScalarType "int4"), ("b", ScalarType "int4")])]
select a.b,b.c from 
(select 1 as a, 2 as b) a 
natural inner join (select 3 as a, 4 as c) b;
 
Right
  [Just ([], [("b", ScalarType "int4"), ("c", ScalarType "int4")])]
select a.a,b.a from 
(select 1 as a, 2 as b) a 
natural inner join (select 3 as a, 4 as c) b;
 
Right
  [Just ([], [("a", ScalarType "int4"), ("a", ScalarType "int4")])]
select pg_attrdef.adsrc from pg_attrdef;
 
Right [Just ([], [("adsrc", ScalarType "text")])]
select a.adsrc from pg_attrdef a;
 
Right [Just ([], [("adsrc", ScalarType "text")])]
select pg_attrdef.adsrc from pg_attrdef a;
 
Left [UnrecognisedCorrelationName "pg_attrdef"]
select a from (select 2 as b, 1 as a) a
natural inner join (select 4.5 as d, 1 as a) b;
 
Right [Just ([], [("a", ScalarType "int4")])]

misc selects

aggregates

select max(prorettype::int) from pg_proc;
 
Right [Just ([], [("max", ScalarType "int4")])]

simple wheres

select 1 from pg_type where true;
 
Right [Just ([], [("?column?", ScalarType "int4")])]
select 1 from pg_type where 1;
 
Left [ExpressionMustBeBool]
select typname from pg_type where typbyval;
 
Right [Just ([], [("typname", ScalarType "name")])]
select typname from pg_type where typtype = 'b';
 
Right [Just ([], [("typname", ScalarType "name")])]
select typname from pg_type where what = 'b';
 
Left [UnrecognisedIdentifier "what"]

unnest

select conname,unnest(conkey) as attnum from pg_constraint;
 
Right
  [Just
     ([],
      [("conname", ScalarType "name"), ("attnum", ScalarType "int2")])]

subqueries

select relname as relvar_name
    from pg_class
    where ((relnamespace =
           (select oid
              from pg_namespace
              where (nspname = 'public'))) and (relkind = 'r'));
 
Right [Just ([], [("relvar_name", ScalarType "name")])]
select relname from pg_class where relkind in ('r', 'v');
 
Right [Just ([], [("relname", ScalarType "name")])]
select * from generate_series(1,7) g
where g not in (select * from generate_series(3,5));
 
Right [Just ([], [("g", ScalarType "int4")])]
select * from generate_series(3,5);
 
Right [Just ([], [("generate_series", ScalarType "int4")])]
select * from generate_series(1,7) g
where g not in (1,2);
 
Right [Just ([], [("g", ScalarType "int4")])]
select 3 = any(array[1,2,3]);
 
Right [Just ([], [("?column?", ScalarType "bool")])]
[CatCreateTable "a" [("t", ScalarType "int4")] [],
 CatCreateTable "b"
   [("t", ScalarType "int4"), ("u", ScalarType "numeric")]
   []]
select b.t,b.u from a,b where a.t = b.t
 and b.u = (select min(b.u) from b where a.t = b.t);
 
Right
  [Just
     ([], [("t", ScalarType "int4"), ("u", ScalarType "numeric")])]

select part identifiers

select relname as relvar_name,attname as name from pg_class
inner join pg_attribute
on pg_attribute.attrelid = pg_class.oid;
 
Right
  [Just
     ([],
      [("relvar_name", ScalarType "name"), ("name", ScalarType "name")])]

insert

insert into nope (a,b) values (c,d);
 
Left
  [UnrecognisedRelation "nope", UnrecognisedIdentifier "c",
   UnrecognisedIdentifier "d"]
insert into pg_attrdef (adrelid,adnum,adbin,adsrc)
values (1,2, 'a', 'b');
 
Right [Just ([], [])]
insert into pg_attrdef
values (1,2, 'a', 'c');
 
Right [Just ([], [])]
insert into pg_attrdef (hello,adnum,adbin,adsrc)
values (1,2, 'a', 'b');
 
Left [UnrecognisedIdentifier "hello"]
insert into pg_attrdef (adrelid,adnum,adbin,adsrc)
values (1,true, 'a', 'b');
 
Left [IncompatibleTypes (ScalarType "int2") (ScalarType "bool")]
insert into pg_attrdef (adrelid,adnum,adbin,adsrc)
values (1,true, 'a', 'b','c');
 
Left [WrongNumberOfColumns]

update

update nope set a = 1;
 
Left [UnrecognisedRelation "nope"]
update pg_attrdef set adsrc = '' where 1;
 
Left [ExpressionMustBeBool]
update pg_attrdef set (adbin,adsrc) = ('a','b','c');
 
Left
  [IncompatibleTypes
     (AnonymousRecordType
        [ScalarType "pg_node_tree", ScalarType "text"])
     (AnonymousRecordType [UnknownType, UnknownType, UnknownType])]
update pg_attrdef set (adrelid,adsrc) = (true,'b');
 
Left
  [IncompatibleTypes
     (AnonymousRecordType [ScalarType "oid", ScalarType "text"])
     (AnonymousRecordType [ScalarType "bool", UnknownType])]
update pg_attrdef set (shmadrelid,adsrc) = ('a','b');
 
Left [UnrecognisedIdentifier "shmadrelid"]
update pg_attrdef set adsrc='';
 
Right [Just ([], [])]
update pg_attrdef set adsrc='' where 1=2;
 
Right [Just ([], [])]
update pg_attrdef set adsrc='' where adsrc='';
 
Right [Just ([], [])]
update pg_attrdef set adnum = adnum + 1;
 
Right [Just ([], [])]

delete

delete from nope;
 
Left [UnrecognisedRelation "nope"]
delete from pg_attrdef where 1=2;
 
Right [Just ([], [])]
delete from pg_attrdef where 1;
 
Left [ExpressionMustBeBool]
delete from pg_attrdef where adsrc='';
 
Right [Just ([], [])]

creates

create table t1 (
   a int,
   b text
);
 
[CatCreateTable "t1"
   [("a", ScalarType "int4"), ("b", ScalarType "text")]
   [("tableoid", ScalarType "oid"), ("cmax", ScalarType "cid"),
    ("xmax", ScalarType "xid"), ("cmin", ScalarType "cid"),
    ("xmin", ScalarType "xid"), ("ctid", ScalarType "tid")]]
create type t1 as (
   a int,
   b text
);
 
[CatCreateComposite "t1"
   [("a", ScalarType "int4"), ("b", ScalarType "text")]]
create domain t1 as text;
 
[CatCreateDomain (DomainType "t1") (ScalarType "text")]
create view v1 as select * from pg_attrdef;
 
[CatCreateView "v1"
   [("adrelid", ScalarType "oid"), ("adnum", ScalarType "int2"),
    ("adbin", ScalarType "pg_node_tree"),
    ("adsrc", ScalarType "text")]]
create function t1(text) returns text as $$
null;
$$ language sql stable;
 
[CatCreateFunction FunName "t1" [ScalarType "text"]
   (ScalarType "text")
   False]
create language plpgsql;
 
[CatCreateFunction FunName "plpgsql_call_handler" []
   (Pseudo LanguageHandler)
   False,
 CatCreateFunction FunName "plpgsql_validator" [ScalarType "oid"]
   (Pseudo Void)
   False]

plpgsql

check catalog chaining

create function t1() returns void as $$
begin
  null;
end;
$$ language plpgsql stable;
select t1();
 
Right [Nothing, Just ([], [])]
select t1();
create function t1() returns void as $$
begin
  null;
end;
$$ language plpgsql stable;
 
Left [NoMatchingOperator "t1" []]

select into

drop stuff

create function test(a int) returns void as $$
begin
  null;
end
$$ language plpgsql;
 
[CatCreateFunction FunName "test" [ScalarType "int4"] (Pseudo Void)
   False]
create function test(a int) returns void as $$
begin
  null;
end
$$ language plpgsql;
drop function test(int);
 
[]
drop function test(int);
 
[]
drop function if exists test(int);
 
[]

triggers

misc

window fns

select *, row_number() over () from pg_attrdef;
 
Right
  [Just
     ([],
      [("adrelid", ScalarType "oid"), ("adnum", ScalarType "int2"),
       ("adbin", ScalarType "pg_node_tree"), ("adsrc", ScalarType "text"),
       ("row_number", ScalarType "int8")])]



generated on 10/30/11 15:52:45, hssqlppp-0.4.0