Using scalar function as set-returning: bug or feature?

From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Using scalar function as set-returning: bug or feature?
Date: 2018-02-09 07:28:23
Message-ID: 6c8775ab-4d73-7310-9283-eee06275f438@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

I wonder if the following behavior is considered to be a bug in plpgsql
or it is expected result:

create table my_data(id serial primary key, time timestamp, type text);

create or replace function my_insert(type text) RETURNS BOOLEAN
AS
$BODY$
BEGIN
    insert into my_data (time, type) values (now(), type);
    return true;
END
$BODY$
LANGUAGE plpgsql;

create or replace function my_call_insert() RETURNS BOOLEAN
AS
$BODY$
DECLARE
  b boolean;
BEGIN
    select into b from my_insert('from func atx');
    return b;
END
$BODY$
LANGUAGE plpgsql;

select my_call_insert();
 my_call_insert
----------------

(1 row)

================================================

So, if function returning boolean is used in from list, then no error or
warning is produced, but null value is assigned to the target variable.
If this code is rewritten in more natural way:

    b := my_insert('from func atx');
or
   select my_insert('from func atx') into b;

then function returns expected result (true).
I spent some time investigating this code under debugger and looks like
the reason of the problem is that tuple descriptor for the row returned
by my_insert() contains no columns (number of attributes is zero). May
be there are some reasons for such behavior, but I find it quite
confusing and unnatural. I prefer to report error in this case or return
tuple with single column, containing return value of the function.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Masahiko Sawada 2018-02-09 07:34:27 Re: Temporary tables prevent autovacuum, leading to XID wraparound
Previous Message Amit Langote 2018-02-09 07:11:10 Re: [HACKERS] path toward faster partition pruning