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
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 |