'update returning *' returns 0 columns instead of empty row with 2 columns when (i) no rows updated and (ii) when applied to a partitioned table with sub-partition

From: Petr Fedorov <petr(dot)fedorov(at)phystech(dot)edu>
To: pgsql-bugs(at)postgresql(dot)org
Subject: 'update returning *' returns 0 columns instead of empty row with 2 columns when (i) no rows updated and (ii) when applied to a partitioned table with sub-partition
Date: 2019-02-01 14:32:43
Message-ID: 5da6f0f0-1364-1876-6978-907678f89a3e@phystech.edu
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-bugs

Hello,

The following code snippet demonstrates the problem: the first select
passes and the second [select * from testf(FALSE)] fails. I would expect
that select * from testf(...); works without errors in both cases.

begin;

create table test (id integer, data char(1)) partition by list (id)
tablespace pg_default;
create table test_1 partition of test for values in (1) partition by
list (data);
create table test_1_a partition of test_1 for values in ('a');
create function testf(p boolean) returns setof test language 'plpgsql'
as $body$ begin return query update test set id=id where p returning *;
end; $body$;
insert into test (id, data) values (1, 'a');
select * from testf(TRUE);
select * from testf(FALSE);

rollback;   

The result:

ERROR: structure of query does not match function result type

SQL state: 42804

Detail: Number of returned columns (0) does not match expected column
count (2).

Context: PL/pgSQL function testf(boolean) line 1 at RETURN QUERY

I'm on  PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit on Centos 7

uname -a gives   Linux 3.10.0-862.11.6.el7.x86_64 #1 SMP Tue Aug 14
21:49:04 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2019-02-01 17:38:39 BUG #15618: Duplicating a join clause breaks index use
Previous Message Maeldron T. 2019-02-01 14:32:29 FATAL: terminating walreceiver process due to administrator command