Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] PL/pgSQL Function Help

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Michael Fuhr <mike(at)fuhr(dot)org>
Cc: pgsql-bugs(at)postgreSQL(dot)org
Subject: Re: [GENERAL] PL/pgSQL Function Help
Date: 2005-12-16 22:10:11
Message-ID: 29893.1134771011@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-general
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

In response to

Responses

pgsql-bugs by date

Next:From: Jim C. NasbyDate: 2005-12-17 00:05:45
Subject: Re: [GENERAL] PL/pgSQL Function Help
Previous:From: Pierre GirardDate: 2005-12-16 21:35:57
Subject: Re: Solaris cc compiler on amd: PostgreSQL does not have native

pgsql-general by date

Next:From: Chris BrowneDate: 2005-12-16 22:11:09
Subject: Re: Getting a DB password to work without editing pg_hba.conf,
Previous:From: Alvaro HerreraDate: 2005-12-16 22:09:00
Subject: Re: [GENERAL] Transacciones Anidadas

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group