*** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/create_type.out 2010-09-01 15:27:48.257621838 -0700 --- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/create_type.out 2010-09-01 15:30:06.538280313 -0700 *************** *** 86,97 **** CREATE FUNCTION get_default_test() RETURNS SETOF default_test_row AS ' SELECT * FROM default_test; ' LANGUAGE SQL; SELECT * FROM get_default_test(); ! f1 | f2 ! -------+---- ! zippo | 42 ! (1 row) ! -- Test comments COMMENT ON TYPE bad IS 'bad comment'; ERROR: type "bad" does not exist --- 86,98 ---- CREATE FUNCTION get_default_test() RETURNS SETOF default_test_row AS ' SELECT * FROM default_test; ' LANGUAGE SQL; + ERROR: type default_test_row is not composite + CONTEXT: SQL function "get_default_test" SELECT * FROM get_default_test(); ! ERROR: function get_default_test() does not exist ! LINE 1: SELECT * FROM get_default_test(); ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- Test comments COMMENT ON TYPE bad IS 'bad comment'; ERROR: type "bad" does not exist *************** *** 101,107 **** CREATE TYPE text_w_default; -- should fail ERROR: type "text_w_default" already exists DROP TYPE default_test_row CASCADE; - NOTICE: drop cascades to function get_default_test() DROP TABLE default_test; -- Check usage of typmod with a user-defined type -- (we have borrowed numeric's typmod functions) --- 102,107 ---- ====================================================================== *** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/create_function_2.out 2010-09-01 15:29:56.698248597 -0700 --- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/create_function_2.out 2010-09-01 15:30:06.698872691 -0700 *************** *** 5,14 **** --- 5,18 ---- RETURNS setof hobbies_r AS 'select * from hobbies_r where person = $1.name' LANGUAGE SQL; + ERROR: type person is not composite + CONTEXT: SQL function "hobbies" CREATE FUNCTION hobby_construct(text, text) RETURNS hobbies_r AS 'select $1 as name, $2 as hobby' LANGUAGE SQL; + ERROR: type hobbies_r is not composite + CONTEXT: SQL function "hobby_construct" CREATE FUNCTION hobbies_by_name(hobbies_r.name%TYPE) RETURNS hobbies_r.person%TYPE AS 'select person from hobbies_r where name = $1' *************** *** 19,24 **** --- 23,30 ---- RETURNS setof equipment_r AS 'select * from equipment_r where hobby = $1.name' LANGUAGE SQL; + ERROR: type hobbies_r is not composite + CONTEXT: SQL function "equipment" CREATE FUNCTION user_relns() RETURNS setof name AS 'select relname ====================================================================== *** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/create_aggregate.out 2010-09-01 15:27:48.257621838 -0700 --- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/create_aggregate.out 2010-09-01 15:30:07.159497058 -0700 *************** *** 48,61 **** --- 48,67 ---- create function aggf_trans(aggtype[],integer,integer,text) returns aggtype[] as 'select array_append($1,ROW($2,$3,$4)::aggtype)' language sql strict immutable; + ERROR: type aggtype is not composite + CONTEXT: SQL function "aggf_trans" create function aggfns_trans(aggtype[],integer,integer,text) returns aggtype[] as 'select array_append($1,ROW($2,$3,$4)::aggtype)' language sql immutable; + ERROR: type aggtype is not composite + CONTEXT: SQL function "aggfns_trans" create aggregate aggfstr(integer,integer,text) ( sfunc = aggf_trans, stype = aggtype[], initcond = '{}' ); + ERROR: function aggf_trans(aggtype[], integer, integer, text) does not exist create aggregate aggfns(integer,integer,text) ( sfunc = aggfns_trans, stype = aggtype[], initcond = '{}' ); + ERROR: function aggfns_trans(aggtype[], integer, integer, text) does not exist ====================================================================== *** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/inherit.out 2010-09-01 15:27:48.267621881 -0700 --- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/inherit.out 2010-09-01 15:30:07.878872110 -0700 *************** *** 687,711 **** create table derived () inherits (base); insert into derived (i) values (0); select derived::base from derived; ! derived ! --------- ! (0) ! (1 row) ! drop table derived; drop table base; create table p1(ff1 int); create table p2(f1 text); create function p2text(p2) returns text as 'select $1.f1' language sql; create table c1(f3 int) inherits(p1,p2); insert into c1 values(123456789, 'hi', 42); select p2text(c1.*) from c1; ! p2text ! -------- ! hi ! (1 row) ! drop function p2text(p2); drop table c1; drop table p2; drop table p1; --- 687,709 ---- create table derived () inherits (base); insert into derived (i) values (0); select derived::base from derived; ! ERROR: type base is not composite drop table derived; drop table base; create table p1(ff1 int); create table p2(f1 text); create function p2text(p2) returns text as 'select $1.f1' language sql; + ERROR: type p2 is not composite + CONTEXT: SQL function "p2text" create table c1(f3 int) inherits(p1,p2); insert into c1 values(123456789, 'hi', 42); select p2text(c1.*) from c1; ! ERROR: function p2text(c1) does not exist ! LINE 1: select p2text(c1.*) from c1; ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. drop function p2text(p2); + ERROR: function p2text(p2) does not exist drop table c1; drop table p2; drop table p1; ====================================================================== *** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/typed_table.out 2010-09-01 15:27:48.277621964 -0700 --- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/typed_table.out 2010-09-01 15:30:07.159497058 -0700 *************** *** 2,94 **** ERROR: type "nothing" does not exist CREATE TYPE person_type AS (id int, name text); CREATE TABLE persons OF person_type; CREATE TABLE IF NOT EXISTS persons OF person_type; ! NOTICE: relation "persons" already exists, skipping SELECT * FROM persons; ! id | name ! ----+------ ! (0 rows) ! \d persons - Table "public.persons" - Column | Type | Modifiers - --------+---------+----------- - id | integer | - name | text | - Typed table of type: person_type - CREATE FUNCTION get_all_persons() RETURNS SETOF person_type LANGUAGE SQL AS $$ SELECT * FROM persons; $$; SELECT * FROM get_all_persons(); ! id | name ! ----+------ ! (0 rows) ! -- certain ALTER TABLE operations on typed tables are not allowed ALTER TABLE persons ADD COLUMN comment text; ! ERROR: cannot add column to typed table ALTER TABLE persons DROP COLUMN name; ! ERROR: cannot drop column from typed table ALTER TABLE persons RENAME COLUMN id TO num; ! ERROR: cannot rename column of typed table ALTER TABLE persons ALTER COLUMN name TYPE varchar; ! ERROR: cannot alter column type of typed table CREATE TABLE stuff (id int); ALTER TABLE persons INHERIT stuff; ! ERROR: cannot change inheritance of typed table CREATE TABLE personsx OF person_type (myname WITH OPTIONS NOT NULL); -- error ! ERROR: column "myname" does not exist CREATE TABLE persons2 OF person_type ( id WITH OPTIONS PRIMARY KEY, UNIQUE (name) ); ! NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "persons2_pkey" for table "persons2" ! NOTICE: CREATE TABLE / UNIQUE will create implicit index "persons2_name_key" for table "persons2" \d persons2 - Table "public.persons2" - Column | Type | Modifiers - --------+---------+----------- - id | integer | not null - name | text | - Indexes: - "persons2_pkey" PRIMARY KEY, btree (id) - "persons2_name_key" UNIQUE CONSTRAINT, btree (name) - Typed table of type: person_type - CREATE TABLE persons3 OF person_type ( PRIMARY KEY (id), name WITH OPTIONS DEFAULT '' ); ! NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "persons3_pkey" for table "persons3" \d persons3 - Table "public.persons3" - Column | Type | Modifiers - --------+---------+------------------ - id | integer | not null - name | text | default ''::text - Indexes: - "persons3_pkey" PRIMARY KEY, btree (id) - Typed table of type: person_type - CREATE TABLE persons4 OF person_type ( name WITH OPTIONS NOT NULL, name WITH OPTIONS DEFAULT '' -- error, specified more than once ); ! ERROR: column "name" specified more than once DROP TYPE person_type RESTRICT; - ERROR: cannot drop type person_type because other objects depend on it - DETAIL: table persons depends on type person_type - function get_all_persons() depends on type person_type - table persons2 depends on type person_type - table persons3 depends on type person_type - HINT: Use DROP ... CASCADE to drop the dependent objects too. DROP TYPE person_type CASCADE; ! NOTICE: drop cascades to 4 other objects ! DETAIL: drop cascades to table persons ! drop cascades to function get_all_persons() ! drop cascades to table persons2 ! drop cascades to table persons3 DROP TABLE stuff; --- 2,60 ---- ERROR: type "nothing" does not exist CREATE TYPE person_type AS (id int, name text); CREATE TABLE persons OF person_type; + ERROR: type person_type is not composite CREATE TABLE IF NOT EXISTS persons OF person_type; ! ERROR: type person_type is not composite SELECT * FROM persons; ! ERROR: relation "persons" does not exist ! LINE 1: SELECT * FROM persons; ! ^ \d persons CREATE FUNCTION get_all_persons() RETURNS SETOF person_type LANGUAGE SQL AS $$ SELECT * FROM persons; $$; + ERROR: relation "persons" does not exist + LINE 4: SELECT * FROM persons; + ^ SELECT * FROM get_all_persons(); ! ERROR: function get_all_persons() does not exist ! LINE 1: SELECT * FROM get_all_persons(); ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- certain ALTER TABLE operations on typed tables are not allowed ALTER TABLE persons ADD COLUMN comment text; ! ERROR: relation "persons" does not exist ALTER TABLE persons DROP COLUMN name; ! ERROR: relation "persons" does not exist ALTER TABLE persons RENAME COLUMN id TO num; ! ERROR: relation "persons" does not exist ALTER TABLE persons ALTER COLUMN name TYPE varchar; ! ERROR: relation "persons" does not exist CREATE TABLE stuff (id int); ALTER TABLE persons INHERIT stuff; ! ERROR: relation "persons" does not exist CREATE TABLE personsx OF person_type (myname WITH OPTIONS NOT NULL); -- error ! ERROR: type person_type is not composite CREATE TABLE persons2 OF person_type ( id WITH OPTIONS PRIMARY KEY, UNIQUE (name) ); ! ERROR: type person_type is not composite \d persons2 CREATE TABLE persons3 OF person_type ( PRIMARY KEY (id), name WITH OPTIONS DEFAULT '' ); ! ERROR: type person_type is not composite \d persons3 CREATE TABLE persons4 OF person_type ( name WITH OPTIONS NOT NULL, name WITH OPTIONS DEFAULT '' -- error, specified more than once ); ! ERROR: type person_type is not composite DROP TYPE person_type RESTRICT; DROP TYPE person_type CASCADE; ! ERROR: type "person_type" does not exist DROP TABLE stuff; ====================================================================== *** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/subselect.out 2010-09-01 15:27:48.267621881 -0700 --- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/subselect.out 2010-09-01 15:30:09.317623226 -0700 *************** *** 484,512 **** insert into table_a values (42); create temp view view_a as select * from table_a; select view_a from view_a; ! view_a ! -------- ! (42) ! (1 row) ! select (select view_a) from view_a; ! ?column? ! ---------- ! (42) ! (1 row) ! select (select (select view_a)) from view_a; ! ?column? ! ---------- ! (42) ! (1 row) ! select (select (a.*)::text) from view_a a; ! ?column? ! ---------- ! (42) ! (1 row) ! -- -- Test case for sublinks pushed down into subselects via join alias expansion -- --- 484,496 ---- insert into table_a values (42); create temp view view_a as select * from table_a; select view_a from view_a; ! ERROR: type view_a is not composite select (select view_a) from view_a; ! ERROR: type view_a is not composite select (select (select view_a)) from view_a; ! ERROR: type view_a is not composite select (select (a.*)::text) from view_a a; ! ERROR: type view_a is not composite -- -- Test case for sublinks pushed down into subselects via join alias expansion -- ====================================================================== *** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/aggregates.out 2010-09-01 15:27:48.257621838 -0700 --- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/aggregates.out 2010-09-01 15:30:09.267623163 -0700 *************** *** 591,804 **** -- multi-arg aggs, strict/nonstrict, distinct/order by select aggfstr(a,b,c) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); ! aggfstr ! --------------------------------------- ! {"(1,3,foo)","(2,2,bar)","(3,1,baz)"} ! (1 row) ! select aggfns(a,b,c) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); ! aggfns ! ----------------------------------------------- ! {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"} ! (1 row) ! select aggfstr(distinct a,b,c) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,3) i; ! aggfstr ! --------------------------------------- ! {"(1,3,foo)","(2,2,bar)","(3,1,baz)"} ! (1 row) ! select aggfns(distinct a,b,c) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,3) i; ! aggfns ! ----------------------------------------------- ! {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"} ! (1 row) ! select aggfstr(distinct a,b,c order by b) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,3) i; ! aggfstr ! --------------------------------------- ! {"(3,1,baz)","(2,2,bar)","(1,3,foo)"} ! (1 row) ! select aggfns(distinct a,b,c order by b) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,3) i; ! aggfns ! ----------------------------------------------- ! {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"} ! (1 row) ! -- test specific code paths select aggfns(distinct a,a,c order by c using ~<~,a) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,2) i; ! aggfns ! ------------------------------------------------ ! {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"} ! (1 row) ! select aggfns(distinct a,a,c order by c using ~<~) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,2) i; ! aggfns ! ------------------------------------------------ ! {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"} ! (1 row) ! select aggfns(distinct a,a,c order by a) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,2) i; ! aggfns ! ------------------------------------------------ ! {"(0,0,)","(1,1,foo)","(2,2,bar)","(3,3,baz)"} ! (1 row) ! select aggfns(distinct a,b,c order by a,c using ~<~,b) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,2) i; ! aggfns ! ----------------------------------------------- ! {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"} ! (1 row) ! -- check node I/O via view creation and usage, also deparsing logic create view agg_view1 as select aggfns(a,b,c) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); ! select * from agg_view1; ! aggfns ! ----------------------------------------------- ! {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"} ! (1 row) ! ! select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! -------------------------------------------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(v.a, v.b, v.c) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); ! (1 row) ! create or replace view agg_view1 as select aggfns(distinct a,b,c) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,3) i; ! select * from agg_view1; ! aggfns ! ----------------------------------------------- ! {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"} ! (1 row) ! ! select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(DISTINCT v.a, v.b, v.c) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 3) i(i); ! (1 row) ! create or replace view agg_view1 as select aggfns(distinct a,b,c order by b) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,3) i; ! select * from agg_view1; ! aggfns ! ----------------------------------------------- ! {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"} ! (1 row) ! ! select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 3) i(i); ! (1 row) ! create or replace view agg_view1 as select aggfns(a,b,c order by b+1) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); ! select * from agg_view1; ! aggfns ! ----------------------------------------------- ! {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"} ! (1 row) ! ! select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(v.a, v.b, v.c ORDER BY (v.b + 1)) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); ! (1 row) ! create or replace view agg_view1 as select aggfns(a,a,c order by b) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); ! select * from agg_view1; ! aggfns ! ------------------------------------------------ ! {"(3,3,baz)","(2,2,bar)","(1,1,foo)","(0,0,)"} ! (1 row) ! ! select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! --------------------------------------------------------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(v.a, v.a, v.c ORDER BY v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); ! (1 row) ! create or replace view agg_view1 as select aggfns(a,b,c order by c using ~<~) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); ! select * from agg_view1; ! aggfns ! ----------------------------------------------- ! {"(2,2,bar)","(3,1,baz)","(1,3,foo)","(0,,)"} ! (1 row) ! ! select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ! SELECT aggfns(v.a, v.b, v.c ORDER BY v.c USING ~<~ NULLS LAST) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); ! (1 row) ! create or replace view agg_view1 as select aggfns(distinct a,b,c order by a,c using ~<~,b) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,2) i; ! select * from agg_view1; ! aggfns ! ----------------------------------------------- ! {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"} ! (1 row) ! ! select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.a, v.c USING ~<~ NULLS LAST, v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 2) i(i); ! (1 row) ! drop view agg_view1; -- incorrect DISTINCT usage errors select aggfns(distinct a,b,c order by i) from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i; ! ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list LINE 1: select aggfns(distinct a,b,c order by i) ! ^ select aggfns(distinct a,b,c order by a,b+1) from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i; ! ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list LINE 1: select aggfns(distinct a,b,c order by a,b+1) ! ^ select aggfns(distinct a,b,c order by a,b,i,c) from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i; ! ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list LINE 1: select aggfns(distinct a,b,c order by a,b,i,c) ! ^ select aggfns(distinct a,a,c order by a,b) from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i; ! ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list LINE 1: select aggfns(distinct a,a,c order by a,b) ! ^ -- string_agg tests select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a); string_agg --- 591,799 ---- -- multi-arg aggs, strict/nonstrict, distinct/order by select aggfstr(a,b,c) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); ! ERROR: function aggfstr(integer, integer, text) does not exist ! LINE 1: select aggfstr(a,b,c) ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. select aggfns(a,b,c) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); ! ERROR: function aggfns(integer, integer, text) does not exist ! LINE 1: select aggfns(a,b,c) ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. select aggfstr(distinct a,b,c) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,3) i; ! ERROR: function aggfstr(integer, integer, text) does not exist ! LINE 1: select aggfstr(distinct a,b,c) ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. select aggfns(distinct a,b,c) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,3) i; ! ERROR: function aggfns(integer, integer, text) does not exist ! LINE 1: select aggfns(distinct a,b,c) ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. select aggfstr(distinct a,b,c order by b) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,3) i; ! ERROR: function aggfstr(integer, integer, text) does not exist ! LINE 1: select aggfstr(distinct a,b,c order by b) ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. select aggfns(distinct a,b,c order by b) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,3) i; ! ERROR: function aggfns(integer, integer, text) does not exist ! LINE 1: select aggfns(distinct a,b,c order by b) ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- test specific code paths select aggfns(distinct a,a,c order by c using ~<~,a) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,2) i; ! ERROR: function aggfns(integer, integer, text) does not exist ! LINE 1: select aggfns(distinct a,a,c order by c using ~<~,a) ! ^ ! HINT: No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate. select aggfns(distinct a,a,c order by c using ~<~) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,2) i; ! ERROR: function aggfns(integer, integer, text) does not exist ! LINE 1: select aggfns(distinct a,a,c order by c using ~<~) ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. select aggfns(distinct a,a,c order by a) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,2) i; ! ERROR: function aggfns(integer, integer, text) does not exist ! LINE 1: select aggfns(distinct a,a,c order by a) ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. select aggfns(distinct a,b,c order by a,c using ~<~,b) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,2) i; ! ERROR: function aggfns(integer, integer, text) does not exist ! LINE 1: select aggfns(distinct a,b,c order by a,c using ~<~,b) ! ^ ! HINT: No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate. -- check node I/O via view creation and usage, also deparsing logic create view agg_view1 as select aggfns(a,b,c) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); ! ERROR: function aggfns(integer, integer, text) does not exist ! LINE 2: select aggfns(a,b,c) ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. ! select * from agg_view1; ! ERROR: relation "agg_view1" does not exist ! LINE 1: select * from agg_view1; ! ^ ! select pg_get_viewdef('agg_view1'::regclass); ! ERROR: relation "agg_view1" does not exist ! LINE 1: select pg_get_viewdef('agg_view1'::regclass); ! ^ create or replace view agg_view1 as select aggfns(distinct a,b,c) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,3) i; ! ERROR: function aggfns(integer, integer, text) does not exist ! LINE 2: select aggfns(distinct a,b,c) ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. ! select * from agg_view1; ! ERROR: relation "agg_view1" does not exist ! LINE 1: select * from agg_view1; ! ^ ! select pg_get_viewdef('agg_view1'::regclass); ! ERROR: relation "agg_view1" does not exist ! LINE 1: select pg_get_viewdef('agg_view1'::regclass); ! ^ create or replace view agg_view1 as select aggfns(distinct a,b,c order by b) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,3) i; ! ERROR: function aggfns(integer, integer, text) does not exist ! LINE 2: select aggfns(distinct a,b,c order by b) ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. ! select * from agg_view1; ! ERROR: relation "agg_view1" does not exist ! LINE 1: select * from agg_view1; ! ^ ! select pg_get_viewdef('agg_view1'::regclass); ! ERROR: relation "agg_view1" does not exist ! LINE 1: select pg_get_viewdef('agg_view1'::regclass); ! ^ create or replace view agg_view1 as select aggfns(a,b,c order by b+1) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); ! ERROR: function aggfns(integer, integer, text) does not exist ! LINE 2: select aggfns(a,b,c order by b+1) ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. ! select * from agg_view1; ! ERROR: relation "agg_view1" does not exist ! LINE 1: select * from agg_view1; ! ^ ! select pg_get_viewdef('agg_view1'::regclass); ! ERROR: relation "agg_view1" does not exist ! LINE 1: select pg_get_viewdef('agg_view1'::regclass); ! ^ create or replace view agg_view1 as select aggfns(a,a,c order by b) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); ! ERROR: function aggfns(integer, integer, text) does not exist ! LINE 2: select aggfns(a,a,c order by b) ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. ! select * from agg_view1; ! ERROR: relation "agg_view1" does not exist ! LINE 1: select * from agg_view1; ! ^ ! select pg_get_viewdef('agg_view1'::regclass); ! ERROR: relation "agg_view1" does not exist ! LINE 1: select pg_get_viewdef('agg_view1'::regclass); ! ^ create or replace view agg_view1 as select aggfns(a,b,c order by c using ~<~) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); ! ERROR: function aggfns(integer, integer, text) does not exist ! LINE 2: select aggfns(a,b,c order by c using ~<~) ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. ! select * from agg_view1; ! ERROR: relation "agg_view1" does not exist ! LINE 1: select * from agg_view1; ! ^ ! select pg_get_viewdef('agg_view1'::regclass); ! ERROR: relation "agg_view1" does not exist ! LINE 1: select pg_get_viewdef('agg_view1'::regclass); ! ^ create or replace view agg_view1 as select aggfns(distinct a,b,c order by a,c using ~<~,b) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,2) i; ! ERROR: function aggfns(integer, integer, text) does not exist ! LINE 2: select aggfns(distinct a,b,c order by a,c using ~<~,b) ! ^ ! HINT: No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate. ! select * from agg_view1; ! ERROR: relation "agg_view1" does not exist ! LINE 1: select * from agg_view1; ! ^ ! select pg_get_viewdef('agg_view1'::regclass); ! ERROR: relation "agg_view1" does not exist ! LINE 1: select pg_get_viewdef('agg_view1'::regclass); ! ^ drop view agg_view1; + ERROR: view "agg_view1" does not exist -- incorrect DISTINCT usage errors select aggfns(distinct a,b,c order by i) from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i; ! ERROR: function aggfns(integer, integer, text) does not exist LINE 1: select aggfns(distinct a,b,c order by i) ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. select aggfns(distinct a,b,c order by a,b+1) from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i; ! ERROR: function aggfns(integer, integer, text) does not exist LINE 1: select aggfns(distinct a,b,c order by a,b+1) ! ^ ! HINT: No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate. select aggfns(distinct a,b,c order by a,b,i,c) from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i; ! ERROR: function aggfns(integer, integer, text) does not exist LINE 1: select aggfns(distinct a,b,c order by a,b,i,c) ! ^ ! HINT: No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate. select aggfns(distinct a,a,c order by a,b) from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i; ! ERROR: function aggfns(integer, integer, text) does not exist LINE 1: select aggfns(distinct a,a,c order by a,b) ! ^ ! HINT: No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate. -- string_agg tests select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a); string_agg ====================================================================== *** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/arrays.out 2010-09-01 15:27:48.257621838 -0700 --- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/arrays.out 2010-09-01 15:30:09.337622960 -0700 *************** *** 923,942 **** -- XXX would like to not have to specify row() construct types here ... insert into comptable values (row(1,'foo'), array[row(2,'bar')::comptype, row(3,'baz')::comptype]); -- check that implicitly named array type _comptype isn't a problem create type _comptype as enum('fooey'); select * from comptable; ! c1 | c2 ! ---------+----------------------- ! (1,foo) | {"(2,bar)","(3,baz)"} ! (1 row) select c2[2].f2 from comptable; ! f2 ! ----- ! baz ! (1 row) ! drop type _comptype; drop table comptable; drop type comptype; --- 923,938 ---- -- XXX would like to not have to specify row() construct types here ... insert into comptable values (row(1,'foo'), array[row(2,'bar')::comptype, row(3,'baz')::comptype]); + ERROR: type comptype is not composite -- check that implicitly named array type _comptype isn't a problem create type _comptype as enum('fooey'); select * from comptable; ! c1 | c2 ! ----+---- ! (0 rows) select c2[2].f2 from comptable; ! ERROR: type comptype is not composite drop type _comptype; drop table comptable; drop type comptype; *************** *** 1273,1288 **** -- Insert/update on a column that is array of composite create temp table t1 (f1 int8_tbl[]); insert into t1 (f1[5].q1) values(42); select * from t1; ! f1 ! ----------------- ! [5:5]={"(42,)"} ! (1 row) update t1 set f1[5].q2 = 43; select * from t1; ! f1 ! ------------------- ! [5:5]={"(42,43)"} ! (1 row) --- 1269,1283 ---- -- Insert/update on a column that is array of composite create temp table t1 (f1 int8_tbl[]); insert into t1 (f1[5].q1) values(42); + ERROR: type int8_tbl is not composite select * from t1; ! f1 ! ---- ! (0 rows) update t1 set f1[5].q2 = 43; select * from t1; ! f1 ! ---- ! (0 rows) ====================================================================== *** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/misc.out 2010-09-01 15:29:56.698248597 -0700 --- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/misc.out 2010-09-01 15:30:13.858246862 -0700 *************** *** 446,568 **** -- everyone else does nothing. -- SELECT p.name, name(p.hobbies) FROM ONLY person p; ! name | name ! -------+------------- ! mike | posthacking ! joe | basketball ! sally | basketball ! (3 rows) ! -- -- as above, but jeff also does post_hacking. -- SELECT p.name, name(p.hobbies) FROM person* p; ! name | name ! -------+------------- ! mike | posthacking ! joe | basketball ! sally | basketball ! jeff | posthacking ! (4 rows) ! -- -- the next two queries demonstrate how functions generate bogus duplicates. -- this is a "feature" .. -- SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FROM hobbies_r ORDER BY 1,2; ! name | name ! -------------+--------------- ! basketball | hightops ! posthacking | advil ! posthacking | peet's coffee ! skywalking | guts ! (4 rows) ! SELECT hobbies_r.name, (hobbies_r.equipment).name FROM hobbies_r; ! name | name ! -------------+--------------- ! posthacking | advil ! posthacking | peet's coffee ! posthacking | advil ! posthacking | peet's coffee ! basketball | hightops ! basketball | hightops ! skywalking | guts ! (7 rows) ! -- -- mike needs advil and peet's coffee, -- joe and sally need hightops, and -- everyone else is fine. -- SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM ONLY person p; ! name | name | name ! -------+-------------+--------------- ! mike | posthacking | advil ! mike | posthacking | peet's coffee ! joe | basketball | hightops ! sally | basketball | hightops ! (4 rows) ! -- -- as above, but jeff needs advil and peet's coffee as well. -- SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM person* p; ! name | name | name ! -------+-------------+--------------- ! mike | posthacking | advil ! mike | posthacking | peet's coffee ! joe | basketball | hightops ! sally | basketball | hightops ! jeff | posthacking | advil ! jeff | posthacking | peet's coffee ! (6 rows) ! -- -- just like the last two, but make sure that the target list fixup and -- unflattening is being done correctly. -- SELECT name(equipment(p.hobbies)), p.name, name(p.hobbies) FROM ONLY person p; ! name | name | name ! ---------------+-------+------------- ! advil | mike | posthacking ! peet's coffee | mike | posthacking ! hightops | joe | basketball ! hightops | sally | basketball ! (4 rows) ! SELECT (p.hobbies).equipment.name, p.name, name(p.hobbies) FROM person* p; ! name | name | name ! ---------------+-------+------------- ! advil | mike | posthacking ! peet's coffee | mike | posthacking ! hightops | joe | basketball ! hightops | sally | basketball ! advil | jeff | posthacking ! peet's coffee | jeff | posthacking ! (6 rows) ! SELECT (p.hobbies).equipment.name, name(p.hobbies), p.name FROM ONLY person p; ! name | name | name ! ---------------+-------------+------- ! advil | posthacking | mike ! peet's coffee | posthacking | mike ! hightops | basketball | joe ! hightops | basketball | sally ! (4 rows) ! SELECT name(equipment(p.hobbies)), name(p.hobbies), p.name FROM person* p; ! name | name | name ! ---------------+-------------+------- ! advil | posthacking | mike ! peet's coffee | posthacking | mike ! hightops | basketball | joe ! hightops | basketball | sally ! advil | posthacking | jeff ! peet's coffee | posthacking | jeff ! (6 rows) ! SELECT user_relns() AS user_relns ORDER BY user_relns; user_relns --- 446,510 ---- -- everyone else does nothing. -- SELECT p.name, name(p.hobbies) FROM ONLY person p; ! ERROR: column p.hobbies does not exist ! LINE 1: SELECT p.name, name(p.hobbies) FROM ONLY person p; ! ^ -- -- as above, but jeff also does post_hacking. -- SELECT p.name, name(p.hobbies) FROM person* p; ! ERROR: column p.hobbies does not exist ! LINE 1: SELECT p.name, name(p.hobbies) FROM person* p; ! ^ -- -- the next two queries demonstrate how functions generate bogus duplicates. -- this is a "feature" .. -- SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FROM hobbies_r ORDER BY 1,2; ! ERROR: column hobbies_r.equipment does not exist ! LINE 1: SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FR... ! ^ SELECT hobbies_r.name, (hobbies_r.equipment).name FROM hobbies_r; ! ERROR: column hobbies_r.equipment does not exist ! LINE 1: SELECT hobbies_r.name, (hobbies_r.equipment).name FROM hobbi... ! ^ -- -- mike needs advil and peet's coffee, -- joe and sally need hightops, and -- everyone else is fine. -- SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM ONLY person p; ! ERROR: column p.hobbies does not exist ! LINE 1: SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) F... ! ^ -- -- as above, but jeff needs advil and peet's coffee as well. -- SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM person* p; ! ERROR: column p.hobbies does not exist ! LINE 1: SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) F... ! ^ -- -- just like the last two, but make sure that the target list fixup and -- unflattening is being done correctly. -- SELECT name(equipment(p.hobbies)), p.name, name(p.hobbies) FROM ONLY person p; ! ERROR: column p.hobbies does not exist ! LINE 1: SELECT name(equipment(p.hobbies)), p.name, name(p.hobbies) F... ! ^ SELECT (p.hobbies).equipment.name, p.name, name(p.hobbies) FROM person* p; ! ERROR: column p.hobbies does not exist ! LINE 1: SELECT (p.hobbies).equipment.name, p.name, name(p.hobbies) F... ! ^ SELECT (p.hobbies).equipment.name, name(p.hobbies), p.name FROM ONLY person p; ! ERROR: column p.hobbies does not exist ! LINE 1: SELECT (p.hobbies).equipment.name, name(p.hobbies), p.name F... ! ^ SELECT name(equipment(p.hobbies)), name(p.hobbies), p.name FROM person* p; ! ERROR: column p.hobbies does not exist ! LINE 1: SELECT name(equipment(p.hobbies)), name(p.hobbies), p.name F... ! ^ SELECT user_relns() AS user_relns ORDER BY user_relns; user_relns *************** *** 672,682 **** (102 rows) SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))); ! name ! ------ ! guts ! (1 row) ! SELECT hobbies_by_name('basketball'); hobbies_by_name ----------------- --- 614,623 ---- (102 rows) SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))); ! ERROR: function hobby_construct(text, text) does not exist ! LINE 1: SELECT name(equipment(hobby_construct(text 'skywalking', tex... ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT hobbies_by_name('basketball'); hobbies_by_name ----------------- *************** *** 698,738 **** -- Try a few cases with SQL-spec row constructor expressions -- SELECT * FROM equipment(ROW('skywalking', 'mer')); ! name | hobby ! ------+------------ ! guts | skywalking ! (1 row) ! SELECT name(equipment(ROW('skywalking', 'mer'))); ! name ! ------ ! guts ! (1 row) ! SELECT *, name(equipment(h.*)) FROM hobbies_r h; ! name | person | name ! -------------+--------+--------------- ! posthacking | mike | advil ! posthacking | mike | peet's coffee ! posthacking | jeff | advil ! posthacking | jeff | peet's coffee ! basketball | joe | hightops ! basketball | sally | hightops ! skywalking | | guts ! (7 rows) ! SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h; ! name | person | name ! -------------+--------+--------------- ! posthacking | mike | advil ! posthacking | mike | peet's coffee ! posthacking | jeff | advil ! posthacking | jeff | peet's coffee ! basketball | joe | hightops ! basketball | sally | hightops ! skywalking | | guts ! (7 rows) ! -- -- check that old-style C functions work properly with TOASTed values -- --- 639,663 ---- -- Try a few cases with SQL-spec row constructor expressions -- SELECT * FROM equipment(ROW('skywalking', 'mer')); ! ERROR: function equipment(record) does not exist ! LINE 1: SELECT * FROM equipment(ROW('skywalking', 'mer')); ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT name(equipment(ROW('skywalking', 'mer'))); ! ERROR: function equipment(record) does not exist ! LINE 1: SELECT name(equipment(ROW('skywalking', 'mer'))); ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT *, name(equipment(h.*)) FROM hobbies_r h; ! ERROR: function equipment(hobbies_r) does not exist ! LINE 1: SELECT *, name(equipment(h.*)) FROM hobbies_r h; ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h; ! ERROR: function equipment(hobbies_r) does not exist ! LINE 1: SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM ho... ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- -- check that old-style C functions work properly with TOASTed values -- ====================================================================== *** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/plpgsql.out 2010-09-01 15:27:48.267621881 -0700 --- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/plpgsql.out 2010-09-01 15:30:19.687639012 -0700 *************** *** 1631,1646 **** RETURN; END;' language plpgsql; select * from test_table_func_rec(); ! a ! ----- ! 2 ! 100 ! 3 ! 4 ! 5 ! 6 ! (6 rows) ! create function test_table_func_row() returns setof found_test_tbl as ' DECLARE row found_test_tbl%ROWTYPE; --- 1631,1637 ---- RETURN; END;' language plpgsql; select * from test_table_func_rec(); ! ERROR: type found_test_tbl is not composite create function test_table_func_row() returns setof found_test_tbl as ' DECLARE row found_test_tbl%ROWTYPE; *************** *** 1651,1666 **** RETURN; END;' language plpgsql; select * from test_table_func_row(); ! a ! ----- ! 2 ! 100 ! 3 ! 4 ! 5 ! 6 ! (6 rows) ! create function test_ret_set_scalar(int,int) returns setof int as ' DECLARE i int; --- 1642,1648 ---- RETURN; END;' language plpgsql; select * from test_table_func_row(); ! ERROR: type found_test_tbl is not composite create function test_ret_set_scalar(int,int) returns setof int as ' DECLARE i int; *************** *** 2429,2442 **** return _v; end; $$ language plpgsql; select execute_into_test('eifoo'); ! NOTICE: 10 1 ! NOTICE: 10 15 ! NOTICE: 10 15 20 ! execute_into_test ! ------------------- ! (1,2) ! (1 row) ! drop table eifoo cascade; drop type eitype cascade; -- --- 2411,2419 ---- return _v; end; $$ language plpgsql; select execute_into_test('eifoo'); ! ERROR: type eifoo is not composite ! CONTEXT: SQL statement "select (row).* from (select row(10,1)::eifoo) s" ! PL/pgSQL function "execute_into_test" line 11 at EXECUTE statement drop table eifoo cascade; drop type eitype cascade; -- *************** *** 3182,3200 **** end; $$ language plpgsql; select * from ret_query2(8); ! x | y | z ! ----------------------------------+----+--- ! a8d2ec85eaf98407310b72eb73dda247 | -8 | f ! 596a3d04481816330f07e4f97510c28f | -6 | f ! 0267aaf632e87a63288a08331f22c7c3 | -4 | f ! 5d7b9adcbe1c629ec722529dd12e5129 | -2 | f ! cfcd208495d565ef66e7dff9f98764da | 0 | f ! c81e728d9d4c2f636f067f89cc14862c | 2 | t ! a87ff679a2f3e71d9181a67b7542122c | 4 | t ! 1679091c5a880faf6fb5e6087eb1b2dc | 6 | t ! c9f0f895fb98ab9159f51fd0297e236d | 8 | t ! (9 rows) ! -- test EXECUTE USING create function exc_using(int, text) returns int as $$ declare i int; --- 3159,3165 ---- end; $$ language plpgsql; select * from ret_query2(8); ! ERROR: type record_type is not composite -- test EXECUTE USING create function exc_using(int, text) returns int as $$ declare i int; *************** *** 3439,3482 **** end; $$ language plpgsql; select * from returnqueryf(); ! a | b | c | d ! ----+----+----+---- ! 10 | 20 | 30 | 40 ! 50 | 60 | 70 | 80 ! 10 | 20 | 30 | 40 ! 50 | 60 | 70 | 80 ! (4 rows) ! alter table tabwithcols drop column b; select * from returnqueryf(); ! a | c | d ! ----+----+---- ! 10 | 30 | 40 ! 50 | 70 | 80 ! 10 | 30 | 40 ! 50 | 70 | 80 ! (4 rows) ! alter table tabwithcols drop column d; select * from returnqueryf(); ! a | c ! ----+---- ! 10 | 30 ! 50 | 70 ! 10 | 30 ! 50 | 70 ! (4 rows) ! alter table tabwithcols add column d int; select * from returnqueryf(); ! a | c | d ! ----+----+--- ! 10 | 30 | ! 50 | 70 | ! 10 | 30 | ! 50 | 70 | ! (4 rows) ! drop function returnqueryf(); drop table tabwithcols; -- Tests for 8.4's new RAISE features --- 3404,3419 ---- end; $$ language plpgsql; select * from returnqueryf(); ! ERROR: type tabwithcols is not composite alter table tabwithcols drop column b; select * from returnqueryf(); ! ERROR: type tabwithcols is not composite alter table tabwithcols drop column d; select * from returnqueryf(); ! ERROR: type tabwithcols is not composite alter table tabwithcols add column d int; select * from returnqueryf(); ! ERROR: type tabwithcols is not composite drop function returnqueryf(); drop table tabwithcols; -- Tests for 8.4's new RAISE features *************** *** 4130,4141 **** end; $$ language plpgsql; select * from conflict_test(); ! ERROR: column reference "q1" is ambiguous ! LINE 1: select q1,q2 from int8_tbl ! ^ ! DETAIL: It could refer to either a PL/pgSQL variable or a table column. ! QUERY: select q1,q2 from int8_tbl ! CONTEXT: PL/pgSQL function "conflict_test" line 5 at FOR over SELECT rows create or replace function conflict_test() returns setof int8_tbl as $$ #variable_conflict use_variable declare r record; --- 4067,4073 ---- end; $$ language plpgsql; select * from conflict_test(); ! ERROR: type int8_tbl is not composite create or replace function conflict_test() returns setof int8_tbl as $$ #variable_conflict use_variable declare r record; *************** *** 4147,4161 **** end; $$ language plpgsql; select * from conflict_test(); ! q1 | q2 ! ----+------------------- ! 42 | 456 ! 42 | 4567890123456789 ! 42 | 123 ! 42 | 4567890123456789 ! 42 | -4567890123456789 ! (5 rows) ! create or replace function conflict_test() returns setof int8_tbl as $$ #variable_conflict use_column declare r record; --- 4079,4085 ---- end; $$ language plpgsql; select * from conflict_test(); ! ERROR: type int8_tbl is not composite create or replace function conflict_test() returns setof int8_tbl as $$ #variable_conflict use_column declare r record; *************** *** 4167,4181 **** end; $$ language plpgsql; select * from conflict_test(); ! q1 | q2 ! ------------------+------------------- ! 123 | 456 ! 123 | 4567890123456789 ! 4567890123456789 | 123 ! 4567890123456789 | 4567890123456789 ! 4567890123456789 | -4567890123456789 ! (5 rows) ! drop function conflict_test(); -- Check that an unreserved keyword can be used as a variable name create function unreserved_test() returns int as $$ --- 4091,4097 ---- end; $$ language plpgsql; select * from conflict_test(); ! ERROR: type int8_tbl is not composite drop function conflict_test(); -- Check that an unreserved keyword can be used as a variable name create function unreserved_test() returns int as $$ ====================================================================== *** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/domain.out 2010-09-01 15:27:48.257621838 -0700 --- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/domain.out 2010-09-01 15:30:16.827623160 -0700 *************** *** 473,483 **** --- 473,487 ---- create type ddtest1 as (f1 posint); create table ddtest2(f1 ddtest1); insert into ddtest2 values(row(-1)); + ERROR: type ddtest1 is not composite alter domain posint add constraint c1 check(value >= 0); ERROR: cannot alter type "posint" because column "ddtest2"."f1" uses it drop table ddtest2; create table ddtest2(f1 ddtest1[]); insert into ddtest2 values('{(-1)}'); + ERROR: type ddtest1 is not composite + LINE 1: insert into ddtest2 values('{(-1)}'); + ^ alter domain posint add constraint c1 check(value >= 0); ERROR: cannot alter type "posint" because column "ddtest2"."f1" uses it drop table ddtest2; ====================================================================== *** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/rangefuncs.out 2010-09-01 15:27:48.267621881 -0700 --- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/rangefuncs.out 2010-09-01 15:30:16.827623160 -0700 *************** *** 18,61 **** INSERT INTO foo2 VALUES(2, 22); INSERT INTO foo2 VALUES(1, 111); CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL; -- supposed to fail with ERROR select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2; ! ERROR: function expression in FROM cannot refer to other relations of same query level LINE 1: select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2; ! ^ -- function in subselect select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2; ! fooid | f2 ! -------+----- ! 1 | 11 ! 1 | 111 ! 2 | 22 ! (3 rows) ! -- function in subselect select * from foo2 where f2 in (select f2 from foot(1) z where z.fooid = foo2.fooid) ORDER BY 1,2; ! fooid | f2 ! -------+----- ! 1 | 11 ! 1 | 111 ! (2 rows) ! -- function in subselect select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = 1) ORDER BY 1,2; ! fooid | f2 ! -------+----- ! 1 | 11 ! 1 | 111 ! (2 rows) ! -- nested functions select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2; ! fooid | f2 ! -------+----- ! 1 | 11 ! 1 | 111 ! (2 rows) ! CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" INSERT INTO foo VALUES(1,1,'Joe'); --- 18,55 ---- INSERT INTO foo2 VALUES(2, 22); INSERT INTO foo2 VALUES(1, 111); CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL; + ERROR: type foo2 is not composite + CONTEXT: SQL function "foot" -- supposed to fail with ERROR select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2; ! ERROR: function foot(integer) does not exist LINE 1: select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2; ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- function in subselect select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2; ! ERROR: function foot(integer) does not exist ! LINE 1: select * from foo2 where f2 in (select f2 from foot(foo2.foo... ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- function in subselect select * from foo2 where f2 in (select f2 from foot(1) z where z.fooid = foo2.fooid) ORDER BY 1,2; ! ERROR: function foot(integer) does not exist ! LINE 1: select * from foo2 where f2 in (select f2 from foot(1) z whe... ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- function in subselect select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = 1) ORDER BY 1,2; ! ERROR: function foot(integer) does not exist ! LINE 1: select * from foo2 where f2 in (select f2 from foot(foo2.foo... ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- nested functions select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2; ! ERROR: function foot(integer) does not exist ! LINE 1: select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER... ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" INSERT INTO foo VALUES(1,1,'Joe'); *************** *** 118,158 **** DROP VIEW vw_getfoo; DROP FUNCTION getfoo(int); CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL; SELECT * FROM getfoo(1) AS t1; ! fooid | foosubid | fooname ! -------+----------+--------- ! 1 | 1 | Joe ! (1 row) ! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); SELECT * FROM vw_getfoo; ! fooid | foosubid | fooname ! -------+----------+--------- ! 1 | 1 | Joe ! (1 row) ! -- sql, proretset = t, prorettype = c DROP VIEW vw_getfoo; DROP FUNCTION getfoo(int); CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL; SELECT * FROM getfoo(1) AS t1; ! fooid | foosubid | fooname ! -------+----------+--------- ! 1 | 1 | Joe ! 1 | 2 | Ed ! (2 rows) ! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); SELECT * FROM vw_getfoo; ! fooid | foosubid | fooname ! -------+----------+--------- ! 1 | 1 | Joe ! 1 | 2 | Ed ! (2 rows) ! -- sql, proretset = f, prorettype = record DROP VIEW vw_getfoo; DROP FUNCTION getfoo(int); CREATE FUNCTION getfoo(int) RETURNS RECORD AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL; SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text); fooid | foosubid | fooname --- 112,160 ---- DROP VIEW vw_getfoo; DROP FUNCTION getfoo(int); CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL; + ERROR: type foo is not composite + CONTEXT: SQL function "getfoo" SELECT * FROM getfoo(1) AS t1; ! ERROR: function getfoo(integer) does not exist ! LINE 1: SELECT * FROM getfoo(1) AS t1; ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); + ERROR: function getfoo(integer) does not exist + LINE 1: CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); + ^ + HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM vw_getfoo; ! ERROR: relation "vw_getfoo" does not exist ! LINE 1: SELECT * FROM vw_getfoo; ! ^ -- sql, proretset = t, prorettype = c DROP VIEW vw_getfoo; + ERROR: view "vw_getfoo" does not exist DROP FUNCTION getfoo(int); + ERROR: function getfoo(integer) does not exist CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL; + ERROR: type foo is not composite + CONTEXT: SQL function "getfoo" SELECT * FROM getfoo(1) AS t1; ! ERROR: function getfoo(integer) does not exist ! LINE 1: SELECT * FROM getfoo(1) AS t1; ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); + ERROR: function getfoo(integer) does not exist + LINE 1: CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); + ^ + HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM vw_getfoo; ! ERROR: relation "vw_getfoo" does not exist ! LINE 1: SELECT * FROM vw_getfoo; ! ^ -- sql, proretset = f, prorettype = record DROP VIEW vw_getfoo; + ERROR: view "vw_getfoo" does not exist DROP FUNCTION getfoo(int); + ERROR: function getfoo(integer) does not exist CREATE FUNCTION getfoo(int) RETURNS RECORD AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL; SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text); fooid | foosubid | fooname *************** *** 210,230 **** DROP FUNCTION getfoo(int); CREATE FUNCTION getfoo(int) RETURNS foo AS 'DECLARE footup foo%ROWTYPE; BEGIN SELECT * into footup FROM foo WHERE fooid = $1; RETURN footup; END;' LANGUAGE plpgsql; SELECT * FROM getfoo(1) AS t1; ! fooid | foosubid | fooname ! -------+----------+--------- ! 1 | 1 | Joe ! (1 row) ! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); SELECT * FROM vw_getfoo; ! fooid | foosubid | fooname ! -------+----------+--------- ! 1 | 1 | Joe ! (1 row) ! DROP VIEW vw_getfoo; DROP FUNCTION getfoo(int); DROP FUNCTION foot(int); DROP TABLE foo2; DROP TABLE foo; -- Rescan tests -- --- 212,229 ---- DROP FUNCTION getfoo(int); CREATE FUNCTION getfoo(int) RETURNS foo AS 'DECLARE footup foo%ROWTYPE; BEGIN SELECT * into footup FROM foo WHERE fooid = $1; RETURN footup; END;' LANGUAGE plpgsql; SELECT * FROM getfoo(1) AS t1; ! ERROR: type foo is not composite CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); + ERROR: type foo is not composite SELECT * FROM vw_getfoo; ! ERROR: relation "vw_getfoo" does not exist ! LINE 1: SELECT * FROM vw_getfoo; ! ^ DROP VIEW vw_getfoo; + ERROR: view "vw_getfoo" does not exist DROP FUNCTION getfoo(int); DROP FUNCTION foot(int); + ERROR: function foot(integer) does not exist DROP TABLE foo2; DROP TABLE foo; -- Rescan tests -- *************** *** 281,319 **** INSERT INTO foorescan values(5008,5,'abc.5008.5'); INSERT INTO foorescan values(5009,5,'abc.5009.5'); CREATE FUNCTION foorescan(int,int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid >= $1 and fooid < $2 ;' LANGUAGE SQL; --invokes ExecReScanFunctionScan SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(5002,5004)) ORDER BY 1,2; ! fooid | foosubid | fooname ! -------+----------+------------ ! 5002 | 1 | abc.5002.1 ! 5002 | 2 | abc.5002.2 ! 5002 | 3 | abc.5002.3 ! 5002 | 4 | abc.5002.4 ! 5002 | 5 | abc.5002.5 ! 5003 | 1 | abc.5003.1 ! 5003 | 2 | abc.5003.2 ! 5003 | 3 | abc.5003.3 ! 5003 | 4 | abc.5003.4 ! 5003 | 5 | abc.5003.5 ! (10 rows) ! CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,5004); --invokes ExecReScanFunctionScan SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM vw_foorescan) ORDER BY 1,2; ! fooid | foosubid | fooname ! -------+----------+------------ ! 5002 | 1 | abc.5002.1 ! 5002 | 2 | abc.5002.2 ! 5002 | 3 | abc.5002.3 ! 5002 | 4 | abc.5002.4 ! 5002 | 5 | abc.5002.5 ! 5003 | 1 | abc.5003.1 ! 5003 | 2 | abc.5003.2 ! 5003 | 3 | abc.5003.3 ! 5003 | 4 | abc.5003.4 ! 5003 | 5 | abc.5003.5 ! (10 rows) ! CREATE TABLE barrescan (fooid int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "barrescan_pkey" for table "barrescan" INSERT INTO barrescan values(5003); --- 280,303 ---- INSERT INTO foorescan values(5008,5,'abc.5008.5'); INSERT INTO foorescan values(5009,5,'abc.5009.5'); CREATE FUNCTION foorescan(int,int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid >= $1 and fooid < $2 ;' LANGUAGE SQL; + ERROR: type foorescan is not composite + CONTEXT: SQL function "foorescan" --invokes ExecReScanFunctionScan SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(5002,5004)) ORDER BY 1,2; ! ERROR: function foorescan(integer, integer) does not exist ! LINE 1: ...M foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(... ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,5004); + ERROR: function foorescan(integer, integer) does not exist + LINE 1: CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,500... + ^ + HINT: No function matches the given name and argument types. You might need to add explicit type casts. --invokes ExecReScanFunctionScan SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM vw_foorescan) ORDER BY 1,2; ! ERROR: relation "vw_foorescan" does not exist ! LINE 1: ...M foorescan f WHERE f.fooid IN (SELECT fooid FROM vw_fooresc... ! ^ CREATE TABLE barrescan (fooid int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "barrescan_pkey" for table "barrescan" INSERT INTO barrescan values(5003); *************** *** 323,403 **** INSERT INTO barrescan values(5007); INSERT INTO barrescan values(5008); CREATE FUNCTION foorescan(int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid = $1;' LANGUAGE SQL; --invokes ExecReScanFunctionScan with chgParam != NULL SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDER BY 1,2; ! fooid | foosubid | fooname ! -------+----------+------------ ! 5003 | 1 | abc.5003.1 ! 5003 | 2 | abc.5003.2 ! 5003 | 3 | abc.5003.3 ! 5003 | 4 | abc.5003.4 ! 5003 | 5 | abc.5003.5 ! 5004 | 1 | abc.5004.1 ! 5004 | 2 | abc.5004.2 ! 5004 | 3 | abc.5004.3 ! 5004 | 4 | abc.5004.4 ! 5004 | 5 | abc.5004.5 ! 5005 | 1 | abc.5005.1 ! 5005 | 2 | abc.5005.2 ! 5005 | 3 | abc.5005.3 ! 5005 | 4 | abc.5005.4 ! 5005 | 5 | abc.5005.5 ! 5006 | 1 | abc.5006.1 ! 5006 | 2 | abc.5006.2 ! 5006 | 3 | abc.5006.3 ! 5006 | 4 | abc.5006.4 ! 5006 | 5 | abc.5006.5 ! 5007 | 1 | abc.5007.1 ! 5007 | 2 | abc.5007.2 ! 5007 | 3 | abc.5007.3 ! 5007 | 4 | abc.5007.4 ! 5007 | 5 | abc.5007.5 ! 5008 | 1 | abc.5008.1 ! 5008 | 2 | abc.5008.2 ! 5008 | 3 | abc.5008.3 ! 5008 | 4 | abc.5008.4 ! 5008 | 5 | abc.5008.5 ! (30 rows) ! SELECT b.fooid, max(f.foosubid) FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2; ! fooid | max ! -------+----- ! 5003 | 5 ! 5004 | 5 ! 5005 | 5 ! 5006 | 5 ! 5007 | 5 ! 5008 | 5 ! (6 rows) ! CREATE VIEW fooview1 AS SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDER BY 1,2; SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004; ! fooid | foosubid | fooname ! -------+----------+------------ ! 5004 | 1 | abc.5004.1 ! 5004 | 2 | abc.5004.2 ! 5004 | 3 | abc.5004.3 ! 5004 | 4 | abc.5004.4 ! 5004 | 5 | abc.5004.5 ! (5 rows) ! CREATE VIEW fooview2 AS SELECT b.fooid, max(f.foosubid) AS maxsubid FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2; SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5; ! fooid | maxsubid ! -------+---------- ! 5003 | 5 ! 5004 | 5 ! 5005 | 5 ! 5006 | 5 ! 5007 | 5 ! 5008 | 5 ! (6 rows) ! DROP VIEW vw_foorescan; DROP VIEW fooview1; DROP VIEW fooview2; DROP FUNCTION foorescan(int,int); DROP FUNCTION foorescan(int); DROP TABLE foorescan; DROP TABLE barrescan; -- --- 307,353 ---- INSERT INTO barrescan values(5007); INSERT INTO barrescan values(5008); CREATE FUNCTION foorescan(int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid = $1;' LANGUAGE SQL; + ERROR: type foorescan is not composite + CONTEXT: SQL function "foorescan" --invokes ExecReScanFunctionScan with chgParam != NULL SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDER BY 1,2; ! ERROR: function foorescan(integer) does not exist ! LINE 1: ...fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(... ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT b.fooid, max(f.foosubid) FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2; ! ERROR: function foorescan(integer) does not exist ! LINE 1: ...fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(... ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. CREATE VIEW fooview1 AS SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDER BY 1,2; + ERROR: function foorescan(integer) does not exist + LINE 1: ...fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(... + ^ + HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004; ! ERROR: relation "fooview1" does not exist ! LINE 1: SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004; ! ^ CREATE VIEW fooview2 AS SELECT b.fooid, max(f.foosubid) AS maxsubid FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2; + ERROR: function foorescan(integer) does not exist + LINE 1: ...fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(... + ^ + HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5; ! ERROR: relation "fooview2" does not exist ! LINE 1: SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5; ! ^ DROP VIEW vw_foorescan; + ERROR: view "vw_foorescan" does not exist DROP VIEW fooview1; + ERROR: view "fooview1" does not exist DROP VIEW fooview2; + ERROR: view "fooview2" does not exist DROP FUNCTION foorescan(int,int); + ERROR: function foorescan(integer, integer) does not exist DROP FUNCTION foorescan(int); + ERROR: function foorescan(integer) does not exist DROP TABLE foorescan; DROP TABLE barrescan; -- *************** *** 847,883 **** create or replace function get_first_user() returns users as $$ SELECT * FROM users ORDER BY userid LIMIT 1; $$ language sql stable; SELECT get_first_user(); ! get_first_user ! ---------------- ! (id,email,t) ! (1 row) ! SELECT * FROM get_first_user(); ! userid | email | enabled ! --------+-------+--------- ! id | email | t ! (1 row) ! create or replace function get_users() returns setof users as $$ SELECT * FROM users ORDER BY userid; $$ language sql stable; SELECT get_users(); ! get_users ! ---------------- ! (id,email,t) ! (id2,email2,t) ! (2 rows) ! SELECT * FROM get_users(); ! userid | email | enabled ! --------+--------+--------- ! id | email | t ! id2 | email2 | t ! (2 rows) ! drop function get_first_user(); drop function get_users(); drop table users; -- this won't get inlined because of type coercion, but it shouldn't fail create or replace function foobar() returns setof text as --- 797,833 ---- create or replace function get_first_user() returns users as $$ SELECT * FROM users ORDER BY userid LIMIT 1; $$ language sql stable; + ERROR: type users is not composite + CONTEXT: SQL function "get_first_user" SELECT get_first_user(); ! ERROR: function get_first_user() does not exist ! LINE 1: SELECT get_first_user(); ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM get_first_user(); ! ERROR: function get_first_user() does not exist ! LINE 1: SELECT * FROM get_first_user(); ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. create or replace function get_users() returns setof users as $$ SELECT * FROM users ORDER BY userid; $$ language sql stable; + ERROR: type users is not composite + CONTEXT: SQL function "get_users" SELECT get_users(); ! ERROR: function get_users() does not exist ! LINE 1: SELECT get_users(); ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM get_users(); ! ERROR: function get_users() does not exist ! LINE 1: SELECT * FROM get_users(); ! ^ ! HINT: No function matches the given name and argument types. You might need to add explicit type casts. drop function get_first_user(); + ERROR: function get_first_user() does not exist drop function get_users(); + ERROR: function get_users() does not exist drop table users; -- this won't get inlined because of type coercion, but it shouldn't fail create or replace function foobar() returns setof text as ====================================================================== *** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/rowtypes.out 2010-09-01 15:27:48.267621881 -0700 --- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/rowtypes.out 2010-09-01 15:30:16.377623414 -0700 *************** *** 8,86 **** create type quad as (c1 complex, c2 complex); -- Some simple tests of I/O conversions and row construction select (1.1,2.2)::complex, row((3.3,4.4),(5.5,null))::quad; ! row | row ! -----------+------------------------ ! (1.1,2.2) | ("(3.3,4.4)","(5.5,)") ! (1 row) ! select row('Joe', 'Blow')::fullname, '(Joe,Blow)'::fullname; ! row | fullname ! ------------+------------ ! (Joe,Blow) | (Joe,Blow) ! (1 row) ! select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname; ! fullname | fullname ! ------------------+-------------- ! (Joe,"von Blow") | (Joe,d'Blow) ! (1 row) ! select '(Joe,"von""Blow")'::fullname, E'(Joe,d\\\\Blow)'::fullname; ! fullname | fullname ! -------------------+----------------- ! (Joe,"von""Blow") | (Joe,"d\\Blow") ! (1 row) ! select '(Joe,"Blow,Jr")'::fullname; ! fullname ! ----------------- ! (Joe,"Blow,Jr") ! (1 row) ! select '(Joe,)'::fullname; -- ok, null 2nd column ! fullname ! ---------- ! (Joe,) ! (1 row) ! select '(Joe)'::fullname; -- bad ! ERROR: malformed record literal: "(Joe)" LINE 1: select '(Joe)'::fullname; ^ - DETAIL: Too few columns. select '(Joe,,)'::fullname; -- bad ! ERROR: malformed record literal: "(Joe,,)" LINE 1: select '(Joe,,)'::fullname; ^ - DETAIL: Too many columns. create temp table quadtable(f1 int, q quad); insert into quadtable values (1, ((3.3,4.4),(5.5,6.6))); insert into quadtable values (2, ((null,4.4),(5.5,6.6))); select * from quadtable; ! f1 | q ! ----+--------------------------- ! 1 | ("(3.3,4.4)","(5.5,6.6)") ! 2 | ("(,4.4)","(5.5,6.6)") ! (2 rows) select f1, q.c1 from quadtable; -- fails, q is a table reference ERROR: missing FROM-clause entry for table "q" LINE 1: select f1, q.c1 from quadtable; ^ select f1, (q).c1, (qq.q).c1.i from quadtable qq; ! f1 | c1 | i ! ----+-----------+----- ! 1 | (3.3,4.4) | 4.4 ! 2 | (,4.4) | 4.4 ! (2 rows) ! create temp table people (fn fullname, bd date); insert into people values ('(Joe,Blow)', '1984-01-10'); select * from people; ! fn | bd ! ------------+------------ ! (Joe,Blow) | 01-10-1984 ! (1 row) -- at the moment this will not work due to ALTER TABLE inadequacy: alter table fullname add column suffix text default ''; --- 8,65 ---- create type quad as (c1 complex, c2 complex); -- Some simple tests of I/O conversions and row construction select (1.1,2.2)::complex, row((3.3,4.4),(5.5,null))::quad; ! ERROR: type complex is not composite select row('Joe', 'Blow')::fullname, '(Joe,Blow)'::fullname; ! ERROR: type fullname is not composite select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname; ! ERROR: type fullname is not composite ! LINE 1: select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname... ! ^ select '(Joe,"von""Blow")'::fullname, E'(Joe,d\\\\Blow)'::fullname; ! ERROR: type fullname is not composite ! LINE 1: select '(Joe,"von""Blow")'::fullname, E'(Joe,d\\\\Blow)'::fu... ! ^ select '(Joe,"Blow,Jr")'::fullname; ! ERROR: type fullname is not composite ! LINE 1: select '(Joe,"Blow,Jr")'::fullname; ! ^ select '(Joe,)'::fullname; -- ok, null 2nd column ! ERROR: type fullname is not composite ! LINE 1: select '(Joe,)'::fullname; ! ^ select '(Joe)'::fullname; -- bad ! ERROR: type fullname is not composite LINE 1: select '(Joe)'::fullname; ^ select '(Joe,,)'::fullname; -- bad ! ERROR: type fullname is not composite LINE 1: select '(Joe,,)'::fullname; ^ create temp table quadtable(f1 int, q quad); insert into quadtable values (1, ((3.3,4.4),(5.5,6.6))); + ERROR: type quad is not composite insert into quadtable values (2, ((null,4.4),(5.5,6.6))); + ERROR: type quad is not composite select * from quadtable; ! f1 | q ! ----+--- ! (0 rows) select f1, q.c1 from quadtable; -- fails, q is a table reference ERROR: missing FROM-clause entry for table "q" LINE 1: select f1, q.c1 from quadtable; ^ select f1, (q).c1, (qq.q).c1.i from quadtable qq; ! ERROR: type quad is not composite create temp table people (fn fullname, bd date); insert into people values ('(Joe,Blow)', '1984-01-10'); + ERROR: type fullname is not composite + LINE 1: insert into people values ('(Joe,Blow)', '1984-01-10'); + ^ select * from people; ! fn | bd ! ----+---- ! (0 rows) -- at the moment this will not work due to ALTER TABLE inadequacy: alter table fullname add column suffix text default ''; *************** *** 88,114 **** -- but this should work: alter table fullname add column suffix text default null; select * from people; ! fn | bd ! -------------+------------ ! (Joe,Blow,) | 01-10-1984 ! (1 row) -- test insertion/updating of subfields update people set fn.suffix = 'Jr'; select * from people; ! fn | bd ! ---------------+------------ ! (Joe,Blow,Jr) | 01-10-1984 ! (1 row) insert into quadtable (f1, q.c1.r, q.c2.i) values(44,55,66); select * from quadtable; ! f1 | q ! ----+--------------------------- ! 1 | ("(3.3,4.4)","(5.5,6.6)") ! 2 | ("(,4.4)","(5.5,6.6)") ! 44 | ("(55,)","(,66)") ! (3 rows) -- The object here is to ensure that toasted references inside -- composite values don't cause problems. The large f1 value will --- 67,89 ---- -- but this should work: alter table fullname add column suffix text default null; select * from people; ! fn | bd ! ----+---- ! (0 rows) -- test insertion/updating of subfields update people set fn.suffix = 'Jr'; select * from people; ! fn | bd ! ----+---- ! (0 rows) insert into quadtable (f1, q.c1.r, q.c2.i) values(44,55,66); + ERROR: type quad is not composite select * from quadtable; ! f1 | q ! ----+--- ! (0 rows) -- The object here is to ensure that toasted references inside -- composite values don't cause problems. The large f1 value will *************** *** 116,128 **** create temp table pp (f1 text); insert into pp values (repeat('abcdefghijkl', 100000)); insert into people select ('Jim', f1, null)::fullname, current_date from pp; select (fn).first, substr((fn).last, 1, 20), length((fn).last) from people; ! first | substr | length ! -------+----------------------+--------- ! Joe | Blow | 4 ! Jim | abcdefghijklabcdefgh | 1200000 ! (2 rows) ! -- Test row comparison semantics. Prior to PG 8.2 we did this in a totally -- non-spec-compliant way. select ROW(1,2) < ROW(1,3) as true; --- 91,99 ---- create temp table pp (f1 text); insert into pp values (repeat('abcdefghijkl', 100000)); insert into people select ('Jim', f1, null)::fullname, current_date from pp; + ERROR: type fullname is not composite select (fn).first, substr((fn).last, 1, 20), length((fn).last) from people; ! ERROR: type fullname is not composite -- Test row comparison semantics. Prior to PG 8.2 we did this in a totally -- non-spec-compliant way. select ROW(1,2) < ROW(1,3) as true; ======================================================================