On Mon, Jul 20, 2009 at 11:34 AM, Jaime
Casanova<jcasa...@systemguards.com.ec> wrote:
> On Mon, Jul 20, 2009 at 10:09 AM, Alvaro
>>
>> Getting rid of the check on natts was "ungood" ... it needs to compare
>> the number of undropped columns of both tupdescs.
>>
>
> ah! ok, i see... i will mark the patch as "waiting on author" and then
> will try to fix it myself unless pavel wants to do it himself
>
patch attached
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
AsesorÃa y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
Index: src/pl/plpgsql/src/pl_exec.c
===================================================================
RCS file: /home/postgres/pgrepo/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.245
diff -c -r1.245 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c 18 Jul 2009 19:15:42 -0000 1.245
--- src/pl/plpgsql/src/pl_exec.c 20 Jul 2009 20:59:38 -0000
***************
*** 2284,2289 ****
--- 2284,2294 ----
{
Portal portal;
uint32 processed = 0;
+ int i;
+ bool dropped_columns = false;
+ Datum *dvalues;
+ bool *nulls;
+ int natts;
if (!estate->retisset)
ereport(ERROR,
***************
*** 2308,2318 ****
validate_tupdesc_compat(estate->rettupdesc, portal->tupDesc,
"structure of query does not match function result type");
while (true)
{
MemoryContext old_cxt;
- int i;
SPI_cursor_fetch(portal, true, 50);
if (SPI_processed == 0)
--- 2313,2330 ----
validate_tupdesc_compat(estate->rettupdesc, portal->tupDesc,
"structure of query does not match function result type");
+ natts = estate->rettupdesc->natts;
+
+ if (natts > portal->tupDesc->natts)
+ {
+ dropped_columns = true;
+ dvalues = (Datum *) palloc0(natts * sizeof(Datum));
+ nulls = (bool *) palloc(natts * sizeof(bool));
+ }
while (true)
{
MemoryContext old_cxt;
SPI_cursor_fetch(portal, true, 50);
if (SPI_processed == 0)
***************
*** 2323,2335 ****
{
HeapTuple tuple = SPI_tuptable->vals[i];
! tuplestore_puttuple(estate->tuple_store, tuple);
processed++;
}
MemoryContextSwitchTo(old_cxt);
SPI_freetuptable(SPI_tuptable);
}
SPI_freetuptable(SPI_tuptable);
SPI_cursor_close(portal);
--- 2335,2374 ----
{
HeapTuple tuple = SPI_tuptable->vals[i];
! if (!dropped_columns)
! tuplestore_puttuple(estate->tuple_store, tuple);
! else
! {
! int anum;
! int j = 0;
! bool isnull;
!
! for (anum = 0; anum < natts; anum++)
! {
! if (estate->rettupdesc->attrs[anum]->attisdropped)
! nulls[anum] = true;
! else
! {
! dvalues[anum] = SPI_getbinval(tuple, portal->tupDesc,
! ++j, &isnull);
! nulls[anum] = isnull;
! }
! }
! tuple = heap_form_tuple(estate->rettupdesc, dvalues, nulls);
! tuplestore_puttuple(estate->tuple_store, tuple);
! }
processed++;
}
MemoryContextSwitchTo(old_cxt);
SPI_freetuptable(SPI_tuptable);
}
+
+ if (dropped_columns)
+ {
+ pfree(dvalues);
+ pfree(nulls);
+ }
SPI_freetuptable(SPI_tuptable);
SPI_cursor_close(portal);
***************
*** 5127,5153 ****
validate_tupdesc_compat(TupleDesc expected, TupleDesc returned, const char *msg)
{
int i;
const char *dropped_column_type = gettext_noop("N/A (dropped column)");
if (!expected || !returned)
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("%s", _(msg))));
! if (expected->natts != returned->natts)
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("%s", _(msg)),
! errdetail("Number of returned columns (%d) does not match "
! "expected column count (%d).",
! returned->natts, expected->natts)));
!
for (i = 0; i < expected->natts; i++)
! if (expected->attrs[i]->atttypid != returned->attrs[i]->atttypid)
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("%s", _(msg)),
! errdetail("Returned type %s does not match expected type "
"%s in column \"%s\".",
OidIsValid(returned->attrs[i]->atttypid) ?
format_type_be(returned->attrs[i]->atttypid) :
--- 5166,5197 ----
validate_tupdesc_compat(TupleDesc expected, TupleDesc returned, const char *msg)
{
int i;
+ int j = 0;
const char *dropped_column_type = gettext_noop("N/A (dropped column)");
+ int expected_valid_natts, returned_valid_natts;
if (!expected || !returned)
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("%s", _(msg))));
! expected_valid_natts = expected->natts;
! returned_valid_natts = returned->natts;
for (i = 0; i < expected->natts; i++)
! {
! while (j < returned->natts && returned->attrs[j]->attisdropped)
! {
! returned_valid_natts--;
! j++;
! }
! if (expected->attrs[i]->attisdropped)
! expected_valid_natts--;
! else
! if (expected->attrs[i]->atttypid != returned->attrs[j++]->atttypid)
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("%s", _(msg)),
! errdetail("Returned type %s does not match expected type "
"%s in column \"%s\".",
OidIsValid(returned->attrs[i]->atttypid) ?
format_type_be(returned->attrs[i]->atttypid) :
***************
*** 5156,5161 ****
--- 5200,5215 ----
format_type_be(expected->attrs[i]->atttypid) :
_(dropped_column_type),
NameStr(expected->attrs[i]->attname))));
+ }
+
+ if (expected_valid_natts != returned_valid_natts)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("%s", _(msg)),
+ errdetail("Number of returned columns (%d) does not match "
+ "expected column count (%d).",
+ returned_valid_natts, expected_valid_natts)));
+
}
/* ----------
Index: src/test/regress/expected/plpgsql.out
===================================================================
RCS file: /home/postgres/pgrepo/pgsql/src/test/regress/expected/plpgsql.out,v
retrieving revision 1.71
diff -c -r1.71 plpgsql.out
*** src/test/regress/expected/plpgsql.out 19 Apr 2009 18:52:58 -0000 1.71
--- src/test/regress/expected/plpgsql.out 20 Jul 2009 21:51:01 -0000
***************
*** 3285,3290 ****
--- 3285,3366 ----
(4 rows)
drop function return_dquery();
+ -- fix return query and dropped columns bug
+ create table tabwithcols(a int, b int, c int, d int);
+ insert into tabwithcols values(10,20,30,40),(50,60,70,80);
+ create or replace function returnqueryf()
+ returns setof tabwithcols as $$
+ begin
+ return query select * from tabwithcols;
+ return query execute 'select * from tabwithcols';
+ 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;
+ alter table tabwithcols drop column c;
+ select * from returnqueryf();
+ a | d
+ ----+----
+ 10 | 40
+ 50 | 80
+ 10 | 40
+ 50 | 80
+ (4 rows)
+
+ alter table tabwithcols drop column d;
+ select * from returnqueryf();
+ a
+ ----
+ 10
+ 50
+ 10
+ 50
+ (4 rows)
+
+ alter table tabwithcols add column d int;
+ select * from returnqueryf();
+ a | d
+ ----+---
+ 10 |
+ 50 |
+ 10 |
+ 50 |
+ (4 rows)
+
+ -- better to be sure we don't break anything else
+ alter table tabwithcols add column b date;
+ create function trigger_function() returns trigger as $$
+ begin
+ new.b = current_date + new.a;
+ return new;
+ end;
+ $$language plpgsql;
+ create trigger trg_ins_tabwithcols before insert on tabwithcols
+ for each row execute procedure trigger_function();
+ insert into tabwithcols(a, d) values(30,30),(60,70);
+ select * from returnqueryf();
+ a | d | b
+ ----+----+------------
+ 10 | |
+ 50 | |
+ 30 | 30 | 08-19-2009
+ 60 | 70 | 09-18-2009
+ 10 | |
+ 50 | |
+ 30 | 30 | 08-19-2009
+ 60 | 70 | 09-18-2009
+ (8 rows)
+
+ drop function returnqueryf();
+ drop table tabwithcols;
-- Tests for 8.4's new RAISE features
create or replace function raise_test() returns void as $$
begin
Index: src/test/regress/sql/plpgsql.sql
===================================================================
RCS file: /home/postgres/pgrepo/pgsql/src/test/regress/sql/plpgsql.sql,v
retrieving revision 1.60
diff -c -r1.60 plpgsql.sql
*** src/test/regress/sql/plpgsql.sql 19 Apr 2009 18:52:58 -0000 1.60
--- src/test/regress/sql/plpgsql.sql 20 Jul 2009 21:38:16 -0000
***************
*** 2684,2689 ****
--- 2684,2735 ----
drop function return_dquery();
+ -- fix return query and dropped columns bug
+ create table tabwithcols(a int, b int, c int, d int);
+ insert into tabwithcols values(10,20,30,40),(50,60,70,80);
+
+ create or replace function returnqueryf()
+ returns setof tabwithcols as $$
+ begin
+ return query select * from tabwithcols;
+ return query execute 'select * from tabwithcols';
+ end;
+ $$ language plpgsql;
+
+ select * from returnqueryf();
+
+ alter table tabwithcols drop column b;
+ alter table tabwithcols drop column c;
+
+ select * from returnqueryf();
+
+ alter table tabwithcols drop column d;
+
+ select * from returnqueryf();
+
+ alter table tabwithcols add column d int;
+
+ select * from returnqueryf();
+
+ -- better to be sure we don't break anything else
+ alter table tabwithcols add column b date;
+
+ create function trigger_function() returns trigger as $$
+ begin
+ new.b = current_date + new.a;
+ return new;
+ end;
+ $$language plpgsql;
+
+ create trigger trg_ins_tabwithcols before insert on tabwithcols
+ for each row execute procedure trigger_function();
+ insert into tabwithcols(a, d) values(30,30),(60,70);
+
+ select * from returnqueryf();
+
+ drop function returnqueryf();
+ drop table tabwithcols;
+
-- Tests for 8.4's new RAISE features
create or replace function raise_test() returns void as $$
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
