Thread: BUG #4533: Plpgsql complex type failure

The following bug has been logged online:

Bug reference:      4533
Logged by:          Rod Taylor
Email address:      rod.taylor@gmail.com
PostgreSQL version: 8.3.5
Operating system:   FreeBSD 7.0 Stable
Description:        Plpgsql complex type failure
Details:

The below script should be able to correctly set v_time to the complex type
timestamp_with_precision. It seems to take it as "timestamp with time zone"
instead creating interesting errors like this:

BEGIN
CREATE TYPE
CREATE FUNCTION
psql:/home/rbt/foo.sql:19: ERROR:  invalid input syntax for type timestamp
with time zone: "("2008-11-16 00:00:00-05",day)"
CONTEXT:  PL/pgSQL function "testfunc" line 4 at SQL statement
ROLLBACK


Note, the below continues to fail in the same way using
timestamp_with_precision%ROWTYPE.


BEGIN;

CREATE TYPE timestamp_with_precision AS
( timestamp timestamp with time zone
, timestamp_precision varchar
);

CREATE OR REPLACE FUNCTION testfunc() RETURNS boolean AS $FUNC$
DECLARE
  v_time timestamp_with_precision;
BEGIN
  SELECT (CURRENT_DATE, 'day')::timestamp_with_precision
    INTO v_time;

  RETURN true;
END;
$FUNC$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER RETURNS NULL ON NULL
INPUT;

SELECT testfunc();


ROLLBACK;

"Rod Taylor" <rod.taylor@gmail.com> writes:
> DECLARE
>   v_time timestamp_with_precision;
> BEGIN
>   SELECT (CURRENT_DATE, 'day')::timestamp_with_precision
>     INTO v_time;

You're overthinking the problem, it should just read

  SELECT CURRENT_DATE, 'day'
    INTO v_time;

            regards, tom lane

"Rod Taylor" <rod.taylor@gmail.com> writes:
> That was the short form exhibiting the bug. Selecting the complex
> column from a table into a value results in the same error.

Well, you could do "SELECT t.* INTO v_time".  The fundamental point here
is that when the INTO target is a rowtype variable, plpgsql expects to
assign the columns of the SELECT result to the fields of the variable.
We can't change that without breaking enormous amounts of existing code.

            regards, tom lane

That was the short form exhibiting the bug. Selecting the complex
column from a table into a value results in the same error.


BEGIN;

CREATE TYPE timestamp_with_precision AS
( timestamp timestamp with time zone
, timestamp_precision varchar
);

CREATE TABLE timetest(t timestamp_with_precision);
INSERT INTO timetest VALUES ((CURRENT_DATE, 'day')::timestamp_with_precision);

CREATE OR REPLACE FUNCTION testfunc() RETURNS boolean AS $FUNC$
DECLARE
 v_time timestamp_with_precision;
BEGIN
 SELECT t
   INTO v_time
   FROM timetest;

 RETURN true;
END;
$FUNC$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER RETURNS NULL ON NULL
INPUT;

SELECT testfunc();

ROLLBACK;





On Sat, Nov 22, 2008 at 1:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Rod Taylor" <rod.taylor@gmail.com> writes:
>> DECLARE
>>   v_time timestamp_with_precision;
>> BEGIN
>>   SELECT (CURRENT_DATE, 'day')::timestamp_with_precision
>>     INTO v_time;
>
> You're overthinking the problem, it should just read
>
>  SELECT CURRENT_DATE, 'day'
>    INTO v_time;
>
>                        regards, tom lane
>