regression.diffs
text/x-patch
Filename: regression.diffs
Type: text/x-patch
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: context
| File | + | − |
|---|---|---|
| /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/aggregates.out | 137 | 142 |
| /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/arrays.out | 12 | 17 |
| /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/create_aggregate.out | 6 | 0 |
| /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/create_function_2.out | 6 | 0 |
| /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/create_type.out | 6 | 6 |
| /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/domain.out | 4 | 0 |
| /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/inherit.out | 8 | 10 |
| /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/misc.out | 50 | 125 |
| /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/plpgsql.out | 13 | 97 |
| /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/rangefuncs.out | 122 | 172 |
| /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/rowtypes.out | 40 | 69 |
| /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/subselect.out | 4 | 20 |
| /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/typed_table.out | 22 | 56 |
*** /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;
======================================================================