PL/pgSQL Function Help

"Niblett, David A" <niblettda@gru.com> writes:

Is there no way in Postgres that I can simply not return
anything so I show zero rows?

Make the function return SETOF myrec not just myrec. Then you
can return zero or one (or more) myrec's.

regards, tom lane

On Fri, Dec 16, 2005 at 03:30:01PM -0500, Tom Lane wrote:

"Niblett, David A" <niblettda@gru.com> writes:

Is there no way in Postgres that I can simply not return
anything so I show zero rows?

Make the function return SETOF myrec not just myrec. Then you
can return zero or one (or more) myrec's.

Dunno if this indicates a possible problem, but the function as
posted fails an assertion in an assert-enabled 8.0.5 server (but
not in 8.1.1 or 8.2devel).

test=> SELECT test(0);
server closed the connection unexpectedly

#2 0x001f7e30 in ExceptionalCondition (conditionName=0x220b10 "!(typeId == ( (olddata)->t_choice.t_datum.datum_typeid ))", errorType=0x2208e0 "FailedAssertion",
fileName=0x220868 "tuptoaster.c", lineNumber=830) at assert.c:51
#3 0x00054ac8 in toast_flatten_tuple_attribute (value=2231056, typeId=2230496, typeMod=2230376) at tuptoaster.c:830

--
Michael Fuhr

Michael Fuhr <mike@fuhr.org> writes:

Here's a simplified version:

CREATE TYPE test_type AS (x integer);

CREATE FUNCTION test() RETURNS test_type AS $$
DECLARE
rec record;
BEGIN
SELECT INTO rec 1;
RETURN rec;
END;
$$ LANGUAGE plpgsql;

SELECT test();

I get an assertion failure if rec is declared as a record but not
if it's declared as a test_type. And only in 8.0.5, not in 8.1.1
or 8.2devel.

I find that the lack of an assertion failure in 8.1 is a happenstance of
unrelated changes. The problem is that plpgsql is making no effort at
all to ensure that the record type it returns is the same as it's
declared to return. Here's an interesting variant in CVS tip:

regression=# CREATE TYPE test_type AS (x integer);
CREATE TYPE
regression=# create or replace FUNCTION test() RETURNS test_type AS $$
regression$# DECLARE rec record;
regression$# BEGIN
regression$# SELECT INTO rec 1.1;
regression$# RETURN rec;
regression$# END;
regression$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
regression=# SELECT test();
test
-------
(1.1)
(1 row)

Bit of an odd-looking integer, eh? The lack of a crash is only because
we're not doing anything much with the function result except displaying
it, and since record_out only looks at the record value itself, it
doesn't have any preconceived ideas about what it will find. You can
still get the assert failure from toast_flatten_tuple_attribute though:

regression=# create table tt(f1 test_type);
CREATE TABLE
regression=# insert into tt values(test());
server closed the connection unexpectedly

We need to fix plpgsql to ensure that what it returns is of the expected
record type.

regards, tom lane

On Fri, Dec 16, 2005 at 05:10:11PM -0500, Tom Lane wrote:

We need to fix plpgsql to ensure that what it returns is of the expected
record type.

Should a test be added to regression for this? That's something I should
be able to do...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

I wrote:

Michael Fuhr <mike@fuhr.org> writes:

I get an assertion failure if rec is declared as a record but not
if it's declared as a test_type. And only in 8.0.5, not in 8.1.1
or 8.2devel.

I find that the lack of an assertion failure in 8.1 is a happenstance of
unrelated changes. The problem is that plpgsql is making no effort at
all to ensure that the record type it returns is the same as it's
declared to return.

Patches committed to check this in 8.0 and up. 7.4 and 7.3 seem not to
have an issue.

regards, tom lane