CODEWITHSUNDEEP
postgresqlsyntax-errorplpgsqldatabase-migration
Gyoo
Gyoo

Reputation: 209

PostgreSQL: Syntax Error when trying to access field in array

I am working on a migration from Oracle 9i to PostgreSQL 9.3 and I need to migrate some packages with their content.

I am using Ora2PG for most of the work, but packages need to be migrated manually considering how specific to Oracle the code is.

I have a user defined Type called RelevCaspTyp with the following definition :

CREATE TYPE pkgstesa5152com.relevcasptyp AS (
   -- SOME OTHER DATA
   d1           timestamp,
   d2           timestamp,
   d1min        timestamp,
   d2min        timestamp,
   d1max        timestamp,
   d2max        timestamp,
   -- SOME OTHER DATA
);

Then, I create an array of objects of this type, in another Type :

CREATE TYPE pkgstesa5152com.relevcasptabtyp AS (relevcasptabtyp pkgstesa5152com.relevcasptyp[]);

And finally here's the function that triggers a syntax error :

CREATE OR REPLACE FUNCTION PkgStesa5152Com.calculd1d2 (RelevCaspTab INOUT pkgstesa5152com.RelevCaspTabTyp, i integer) AS $body$
BEGIN
IF RelevCaspTab[i].d1max IS NULL OR RelevCaspTab[i].d1min >= RelevCaspTab[i].d1max THEN
   RelevCaspTab[i].d1 := RelevCaspTab[i].d1min;
ELSE
   RelevCaspTab[i].d1 := RelevCaspTab[i].d1max;
END IF;
IF RelevCaspTab[i].d2max IS NULL OR RelevCaspTab[i].d2min >= RelevCaspTab[i].d2max THEN
   RelevCaspTab[i].d2 := RelevCaspTab[i].d2min;
ELSE
   RelevCaspTab[i].d2 := RelevCaspTab[i].d2max;
END IF;
END;
$body$
LANGUAGE PLPGSQL
;

When I try to import this function in my PostgreSQL database, I get this message :

ERROR:  syntax error at or near "."
LINE 5:    RelevCaspTab[i].d1 := RelevCaspTab[i].d1min;
                          ^

As odd as it looks, it seems that LINE 4 (with the IF/THEN) passes correctly, unless this is yet another false positive and the issue is actually elsewhere.

EDIT : it looks like the problem actually comes from the affectation of a value to RelevCaspTab[i].d1. If I comment all the affectations in the function, it is created successfully.

Upvotes: 2

Views: 621

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

if r[i].d1max is null or r[i].d1min >= r[i].d1max then
   r[i] := (r[i].d1min,r[i].d2,r[i].d1min,r[i].d2min,r[i].d1max,r[i].d2max);

Edit

As suggested in the comments:

create type relevCaspTyp as (
   d1           timestamp,
   d1min        timestamp,
   d1max        timestamp
);

create or replace function calculd1d2 (
    r inout relevCaspTyp[], i integer
) as $body$
declare a relevCaspTyp;
begin
    a := r[i];
    if a.d1max is null or a.d1min >= a.d1max then
       a.d1 := a.d1min;
    else
       a.d1 := a.d1max;
    end if;
    r[i] := a;
end;
$body$
language plpgsql
;

with r (r) as (values (array[('2017-01-01','2017-01-04','2017-01-03')::relevCaspTyp]))
select calculd1d2(r,1)
from r;
                                  calculd1d2                                   
-------------------------------------------------------------------------------
 {"(\"2017-01-04 00:00:00\",\"2017-01-04 00:00:00\",\"2017-01-03 00:00:00\")"}

Upvotes: 1

Related Questions