polymorphism.out

来自「postgresql8.3.4源码,开源数据库」· OUT 代码 · 共 616 行 · 第 1/2 页

  STYPE = anyarray, INITCOND = '{}');
ERROR:  function tfnp(anyarray, anyelement) does not exist
--    P    N        P    P
-- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement)
CREATE AGGREGATE myaggn16a(BASETYPE = anyelement, SFUNC = tf2p,
  STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}');
ERROR:  function tf2p(anyarray, anyelement) does not exist
CREATE AGGREGATE myaggn16b(BASETYPE = anyelement, SFUNC = tf2p,
  STYPE = anyarray, INITCOND = '{}');
ERROR:  function tf2p(anyarray, anyelement) does not exist
--    P    P        N    N
-- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
CREATE AGGREGATE myaggn17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray,
  FINALFUNC = ffnp, INITCOND = '{}');
ERROR:  cannot determine transition data type
DETAIL:  An aggregate using a polymorphic transition type must have at least one polymorphic argument.
--    P    P        N    P
-- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement)
CREATE AGGREGATE myaggn18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray,
  FINALFUNC = ffnp, INITCOND = '{}');
ERROR:  cannot determine transition data type
DETAIL:  An aggregate using a polymorphic transition type must have at least one polymorphic argument.
--    P    P        P    N
-- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int)
CREATE AGGREGATE myaggn19a(BASETYPE = anyelement, SFUNC = tf1p,
  STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}');
ERROR:  function tf1p(anyarray, anyelement) does not exist
--    P    P        P    P
-- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
CREATE AGGREGATE myaggn20a(BASETYPE = anyelement, SFUNC = tfp,
  STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}');
ERROR:  function ffnp(anyarray) does not exist
-- multi-arg polymorphic
CREATE AGGREGATE mysum2(anyelement,anyelement) (SFUNC = sum3,
  STYPE = anyelement, INITCOND = '0');
-- create test data for polymorphic aggregates
create temp table t(f1 int, f2 int[], f3 text);
insert into t values(1,array[1],'a');
insert into t values(1,array[11],'b');
insert into t values(1,array[111],'c');
insert into t values(2,array[2],'a');
insert into t values(2,array[22],'b');
insert into t values(2,array[222],'c');
insert into t values(3,array[3],'a');
insert into t values(3,array[3],'b');
-- test the successfully created polymorphic aggregates
select f3, myaggp01a(*) from t group by f3;
 f3 | myaggp01a 
----+-----------
 b  | {}
 c  | {}
 a  | {}
(3 rows)

select f3, myaggp03a(*) from t group by f3;
 f3 | myaggp03a 
----+-----------
 b  | {}
 c  | {}
 a  | {}
(3 rows)

select f3, myaggp03b(*) from t group by f3;
 f3 | myaggp03b 
----+-----------
 b  | {}
 c  | {}
 a  | {}
(3 rows)

select f3, myaggp05a(f1) from t group by f3;
 f3 | myaggp05a 
----+-----------
 b  | {1,2,3}
 c  | {1,2}
 a  | {1,2,3}
(3 rows)

select f3, myaggp06a(f1) from t group by f3;
 f3 | myaggp06a 
----+-----------
 b  | {}
 c  | {}
 a  | {}
(3 rows)

select f3, myaggp08a(f1) from t group by f3;
 f3 | myaggp08a 
----+-----------
 b  | {}
 c  | {}
 a  | {}
(3 rows)

select f3, myaggp09a(f1) from t group by f3;
 f3 | myaggp09a 
----+-----------
 b  | {}
 c  | {}
 a  | {}
(3 rows)

select f3, myaggp09b(f1) from t group by f3;
 f3 | myaggp09b 
----+-----------
 b  | {}
 c  | {}
 a  | {}
(3 rows)

select f3, myaggp10a(f1) from t group by f3;
 f3 | myaggp10a 
----+-----------
 b  | {1,2,3}
 c  | {1,2}
 a  | {1,2,3}
(3 rows)

select f3, myaggp10b(f1) from t group by f3;
 f3 | myaggp10b 
----+-----------
 b  | {1,2,3}
 c  | {1,2}
 a  | {1,2,3}
(3 rows)

select f3, myaggp20a(f1) from t group by f3;
 f3 | myaggp20a 
----+-----------
 b  | {1,2,3}
 c  | {1,2}
 a  | {1,2,3}
(3 rows)

select f3, myaggp20b(f1) from t group by f3;
 f3 | myaggp20b 
----+-----------
 b  | {1,2,3}
 c  | {1,2}
 a  | {1,2,3}
(3 rows)

select f3, myaggn01a(*) from t group by f3;
 f3 | myaggn01a 
----+-----------
 b  | {}
 c  | {}
 a  | {}
(3 rows)

select f3, myaggn01b(*) from t group by f3;
 f3 | myaggn01b 
----+-----------
 b  | {}
 c  | {}
 a  | {}
(3 rows)

select f3, myaggn03a(*) from t group by f3;
 f3 | myaggn03a 
----+-----------
 b  | {}
 c  | {}
 a  | {}
(3 rows)

select f3, myaggn05a(f1) from t group by f3;
 f3 | myaggn05a 
----+-----------
 b  | {1,2,3}
 c  | {1,2}
 a  | {1,2,3}
(3 rows)

select f3, myaggn05b(f1) from t group by f3;
 f3 | myaggn05b 
----+-----------
 b  | {1,2,3}
 c  | {1,2}
 a  | {1,2,3}
(3 rows)

select f3, myaggn06a(f1) from t group by f3;
 f3 | myaggn06a 
----+-----------
 b  | {}
 c  | {}
 a  | {}
(3 rows)

select f3, myaggn06b(f1) from t group by f3;
 f3 | myaggn06b 
----+-----------
 b  | {}
 c  | {}
 a  | {}
(3 rows)

select f3, myaggn08a(f1) from t group by f3;
 f3 | myaggn08a 
----+-----------
 b  | {}
 c  | {}
 a  | {}
(3 rows)

select f3, myaggn08b(f1) from t group by f3;
 f3 | myaggn08b 
----+-----------
 b  | {}
 c  | {}
 a  | {}
(3 rows)

select f3, myaggn09a(f1) from t group by f3;
 f3 | myaggn09a 
----+-----------
 b  | {}
 c  | {}
 a  | {}
(3 rows)

select f3, myaggn10a(f1) from t group by f3;
 f3 | myaggn10a 
----+-----------
 b  | {1,2,3}
 c  | {1,2}
 a  | {1,2,3}
(3 rows)

select mysum2(f1, f1 + 1) from t;
 mysum2 
--------
     38
(1 row)

-- test inlining of polymorphic SQL functions
create function bleat(int) returns int as $$
begin
  raise notice 'bleat %', $1;
  return $1;
end$$ language plpgsql;
create function sql_if(bool, anyelement, anyelement) returns anyelement as $$
select case when $1 then $2 else $3 end $$ language sql;
-- Note this would fail with integer overflow, never mind wrong bleat() output,
-- if the CASE expression were not successfully inlined
select f1, sql_if(f1 > 0, bleat(f1), bleat(f1 + 1)) from int4_tbl;
NOTICE:  bleat 1
NOTICE:  bleat 123456
NOTICE:  bleat -123455
NOTICE:  bleat 2147483647
NOTICE:  bleat -2147483646
     f1      |   sql_if    
-------------+-------------
           0 |           1
      123456 |      123456
     -123456 |     -123455
  2147483647 |  2147483647
 -2147483647 | -2147483646
(5 rows)

select q2, sql_if(q2 > 0, q2, q2 + 1) from int8_tbl;
        q2         |      sql_if       
-------------------+-------------------
               456 |               456
  4567890123456789 |  4567890123456789
               123 |               123
  4567890123456789 |  4567890123456789
 -4567890123456789 | -4567890123456788
(5 rows)

-- another kind of polymorphic aggregate
create function add_group(grp anyarray, ad anyelement, size integer)
  returns anyarray
  as $$
begin
  if grp is null then
    return array[ad];
  end if;
  if array_upper(grp, 1) < size then
    return grp || ad;
  end if;
  return grp;
end;
$$
  language plpgsql immutable;
create aggregate build_group(anyelement, integer) (
  SFUNC = add_group,
  STYPE = anyarray
);
select build_group(q1,3) from int8_tbl;
        build_group         
----------------------------
 {123,123,4567890123456789}
(1 row)

-- this should fail because stype isn't compatible with arg
create aggregate build_group(int8, integer) (
  SFUNC = add_group,
  STYPE = int2[]
);
ERROR:  function add_group(smallint[], bigint, integer) does not exist
-- but we can make a non-poly agg from a poly sfunc if types are OK
create aggregate build_group(int8, integer) (
  SFUNC = add_group,
  STYPE = int8[]
);