Re: [GENERAL] PL/pgSQL Function Help

Michael Fuhr <mike(at)fuhr(dot)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