HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Is this inconsistent treatment of `void` return from sql and plpgsql functions documented?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
thisfunctionsinconsistentreturnsqldocumentedplpgsqlandtreatmentfrom

Problem

begin;
create table foo(id integer);
create function f1() returns void language sql as $delete from foo;$;
create function f2() returns void language plpgsql as $begin delete from foo; end;$;
select f1() is null, pg_typeof(f1()), f1()::text, f1()::text is null;
/*
 ?column? | pg_typeof | f1 | ?column?
----------+-----------+----+----------
 t        | void      |    | t            <----------------------the return is null
*/
select f2() is null, pg_typeof(f2()), f2()::text, f2()::text='';
/*
 ?column? | pg_typeof | f2 | ?column?
----------+-----------+----+----------
 f        | void      |    | t            <----------------------the return is not null
*/
rollback;


Is this a documented inconsistency?

SQLFiddle here

Solution

I don't believe it's documented. The behavior is incorrect or at best inconsistent but maintained probably because of a huge existing user code base that would break if it were corrected. The issue is caused by differences in how returns VOID is coded with SQL vs plpgsql. See: https://stackoverflow.com/questions/8319986/postgresql-functions-returning-void

Context

StackExchange Database Administrators Q#65310, answer score: 3

Revisions (0)

No revisions yet.