Re: BUG #4533: Plpgsql complex type failure

The following bug has been logged online:

Bug reference: 4533
Logged by: Rod Taylor
Email address: rod(dot)taylor(at)gmail(dot)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(dot)taylor(at)gmail(dot)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

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Rod Taylor" <rod(dot)taylor(at)gmail(dot)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(dot)taylor(at)gmail(dot)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