Re: Schemas not available for pl/pgsql %TYPE.... - Mailing list pgsql-hackers

Tom Lane wrote:
> Sean Chittenden <sean@chittenden.org> writes:
>
>>::sigh:: Is it me or does it look like all
>>of pl/pgsql is schema un-aware (ie, all of the declarations).  -sc
>
>
> Yeah.  The group of routines parse_word, parse_dblword, etc that are
> called by the lexer certainly all need work.  There are some
> definitional issues to think about, too --- plpgsql presently relies on
> the number of names to give it some idea of what to look for, and those
> rules are probably all toast now.  Please come up with a sketch of what
> you think the behavior should be before you start hacking code.

Attached is a diff -c format proposal to fix this. I've also attached a short
test script. Seems to work OK and passes all regression tests.

Here's a breakdown of how I understand plpgsql's "Special word rules" -- I
think it illustrates the behavior reasonably well. New functions added by this
patch are plpgsql_parse_tripwordtype and plpgsql_parse_dblwordrowtype:

============================================================================
Identifiers             (represents)                        parsing function
----------------------------------------------------------------------------
identifier                                                plpgsql_parse_word
     tg_argv
     T_LABEL             (label)
     T_VARIABLE          (variable)
     T_RECORD            (record)
     T_ROW               (row)
----------------------------------------------------------------------------
identifier.identifier                                  plpgsql_parse_dblword
     T_LABEL
         T_VARIABLE      (label.variable)
         T_RECORD        (label.record)
         T_ROW           (label.row)
     T_RECORD
         T_VARIABLE      (record.variable)
     T_ROW
         T_VARIABLE      (row.variable)
----------------------------------------------------------------------------
identifier.identifier.identifier                      plpgsql_parse_tripword
     T_LABEL
         T_RECORD
             T_VARIABLE  (label.record.variable)
         T_ROW
             T_VARIABLE  (label.row.variable)
----------------------------------------------------------------------------
identifier%TYPE                                       plpgsql_parse_wordtype
     T_VARIABLE
         T_DTYPE         (variable%TYPE)
     T_DTYPE             (typname%TYPE)
----------------------------------------------------------------------------
identifier.identifier%TYPE                         plpgsql_parse_dblwordtype
     T_LABEL
         T_VARIABLE
             T_DTYPE     (label.variable%TYPE)
     T_DTYPE             (relname.attname%TYPE)
----------------------------------------------------------------------------
<new>
identifier.identifier.identifier%TYPE             plpgsql_parse_tripwordtype
     T_DTYPE             (nspname.relname.attname%TYPE)
----------------------------------------------------------------------------
identifier%ROWTYPE                                 plpgsql_parse_wordrowtype
     T_DTYPE             (relname%ROWTYPE)
----------------------------------------------------------------------------
<new>
identifier.identifier%ROWTYPE                   plpgsql_parse_dblwordrowtype
     T_DTYPE             (nspname.relname%ROWTYPE)

============================================================================
Parameters - parallels the above
----------------------------------------------------------------------------
$#                                                        plpgsql_parse_word
$#.identifier                                          plpgsql_parse_dblword
$#.identifier.identifier                              plpgsql_parse_tripword
$#%TYPE                                               plpgsql_parse_wordtype
$#.identifier%TYPE                                 plpgsql_parse_dblwordtype
$#.identifier.identifier%TYPE                     plpgsql_parse_tripwordtype
$#%ROWTYPE                                         plpgsql_parse_wordrowtype
$#.identifier%ROWTYPE                           plpgsql_parse_dblwordrowtype

Comments?

Thanks,

Joe
Index: src/pl/plpgsql/src/pl_comp.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/pl/plpgsql/src/pl_comp.c,v
retrieving revision 1.51
diff -c -r1.51 pl_comp.c
*** src/pl/plpgsql/src/pl_comp.c    4 Sep 2002 20:31:47 -0000    1.51
--- src/pl/plpgsql/src/pl_comp.c    9 Sep 2002 04:22:24 -0000
***************
*** 1092,1097 ****
--- 1092,1217 ----
      return T_DTYPE;
  }

+ /* ----------
+  * plpgsql_parse_tripwordtype        Same lookup for word.word.word%TYPE
+  * ----------
+  */
+ #define TYPE_JUNK_LEN    5
+
+ int
+ plpgsql_parse_tripwordtype(char *word)
+ {
+     Oid            classOid;
+     HeapTuple    classtup;
+     Form_pg_class classStruct;
+     HeapTuple    attrtup;
+     Form_pg_attribute attrStruct;
+     HeapTuple    typetup;
+     Form_pg_type typeStruct;
+     PLpgSQL_type *typ;
+     char       *cp[2];
+     int            qualified_att_len;
+     int            numdots = 0;
+     int            i;
+     RangeVar   *relvar;
+
+     /* Do case conversion and word separation */
+     qualified_att_len = strlen(word) - TYPE_JUNK_LEN;
+     Assert(word[qualified_att_len] == '%');
+
+     for (i = 0; i < qualified_att_len; i++)
+     {
+         if (word[i] == '.' && ++numdots == 2)
+         {
+             cp[0] = (char *) palloc((i + 1) * sizeof(char));
+             memset(cp[0], 0, (i + 1) * sizeof(char));
+             memcpy(cp[0], word, i * sizeof(char));
+
+             /* qualified_att_len - one based position + 1 (null terminator) */
+             cp[1] = (char *) palloc((qualified_att_len - i) * sizeof(char));
+             memset(cp[1], 0, (qualified_att_len - i) * sizeof(char));
+             memcpy(cp[1], &word[i + 1], (qualified_att_len - i - 1) * sizeof(char));
+
+             break;
+         }
+     }
+
+     relvar = makeRangeVarFromNameList(stringToQualifiedNameList(cp[0], "plpgsql_parse_dblwordtype"));
+     classOid = RangeVarGetRelid(relvar, true);
+     if (!OidIsValid(classOid))
+     {
+         pfree(cp[0]);
+         pfree(cp[1]);
+         return T_ERROR;
+     }
+     classtup = SearchSysCache(RELOID,
+                               ObjectIdGetDatum(classOid),
+                               0, 0, 0);
+     if (!HeapTupleIsValid(classtup))
+     {
+         pfree(cp[0]);
+         pfree(cp[1]);
+         return T_ERROR;
+     }
+
+     /*
+      * It must be a relation, sequence, view, or type
+      */
+     classStruct = (Form_pg_class) GETSTRUCT(classtup);
+     if (classStruct->relkind != RELKIND_RELATION &&
+         classStruct->relkind != RELKIND_SEQUENCE &&
+         classStruct->relkind != RELKIND_VIEW &&
+         classStruct->relkind != RELKIND_COMPOSITE_TYPE)
+     {
+         ReleaseSysCache(classtup);
+         pfree(cp[0]);
+         pfree(cp[1]);
+         return T_ERROR;
+     }
+
+     /*
+      * Fetch the named table field and it's type
+      */
+     attrtup = SearchSysCacheAttName(classOid, cp[1]);
+     if (!HeapTupleIsValid(attrtup))
+     {
+         ReleaseSysCache(classtup);
+         pfree(cp[0]);
+         pfree(cp[1]);
+         return T_ERROR;
+     }
+     attrStruct = (Form_pg_attribute) GETSTRUCT(attrtup);
+
+     typetup = SearchSysCache(TYPEOID,
+                              ObjectIdGetDatum(attrStruct->atttypid),
+                              0, 0, 0);
+     if (!HeapTupleIsValid(typetup))
+         elog(ERROR, "cache lookup for type %u of %s.%s failed",
+              attrStruct->atttypid, cp[0], cp[1]);
+     typeStruct = (Form_pg_type) GETSTRUCT(typetup);
+
+     /*
+      * Found that - build a compiler type struct and return it
+      */
+     typ = (PLpgSQL_type *) malloc(sizeof(PLpgSQL_type));
+
+     typ->typname = strdup(NameStr(typeStruct->typname));
+     typ->typoid = attrStruct->atttypid;
+     perm_fmgr_info(typeStruct->typinput, &(typ->typinput));
+     typ->typelem = typeStruct->typelem;
+     typ->typbyval = typeStruct->typbyval;
+     typ->typlen = typeStruct->typlen;
+     typ->atttypmod = attrStruct->atttypmod;
+
+     plpgsql_yylval.dtype = typ;
+
+     ReleaseSysCache(classtup);
+     ReleaseSysCache(attrtup);
+     ReleaseSysCache(typetup);
+     pfree(cp[0]);
+     pfree(cp[1]);
+     return T_DTYPE;
+ }

  /* ----------
   * plpgsql_parse_wordrowtype        Scanner found word%ROWTYPE.
***************
*** 1125,1130 ****
--- 1245,1290 ----

      pfree(cp[0]);
      pfree(cp[1]);
+
+     return T_ROW;
+ }
+
+ /* ----------
+  * plpgsql_parse_dblwordrowtype        Scanner found word.word%ROWTYPE.
+  *            So word must be namespace qualified a table name.
+  * ----------
+  */
+ #define ROWTYPE_JUNK_LEN    8
+
+ int
+ plpgsql_parse_dblwordrowtype(char *word)
+ {
+     Oid            classOid;
+     char       *cp;
+     int            i;
+     RangeVar   *relvar;
+
+     /* Do case conversion and word separation */
+     /* We convert %rowtype to .rowtype momentarily to keep converter happy */
+     i = strlen(word) - ROWTYPE_JUNK_LEN;
+     Assert(word[i] == '%');
+
+     cp = (char *) palloc((i + 1) * sizeof(char));
+     memset(cp, 0, (i + 1) * sizeof(char));
+     memcpy(cp, word, i * sizeof(char));
+
+     /* Lookup the relation */
+     relvar = makeRangeVarFromNameList(stringToQualifiedNameList(cp, "plpgsql_parse_dblwordtype"));
+     classOid = RangeVarGetRelid(relvar, true);
+     if (!OidIsValid(classOid))
+         elog(ERROR, "%s: no such class", cp);
+
+     /*
+      * Build and return the complete row definition
+      */
+     plpgsql_yylval.row = build_rowtype(classOid);
+
+     pfree(cp);

      return T_ROW;
  }
Index: src/pl/plpgsql/src/plpgsql.h
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/pl/plpgsql/src/plpgsql.h,v
retrieving revision 1.27
diff -c -r1.27 plpgsql.h
*** src/pl/plpgsql/src/plpgsql.h    4 Sep 2002 20:31:47 -0000    1.27
--- src/pl/plpgsql/src/plpgsql.h    9 Sep 2002 04:21:37 -0000
***************
*** 568,574 ****
--- 568,576 ----
  extern int    plpgsql_parse_tripword(char *word);
  extern int    plpgsql_parse_wordtype(char *word);
  extern int    plpgsql_parse_dblwordtype(char *word);
+ extern int    plpgsql_parse_tripwordtype(char *word);
  extern int    plpgsql_parse_wordrowtype(char *word);
+ extern int    plpgsql_parse_dblwordrowtype(char *word);
  extern PLpgSQL_type *plpgsql_parse_datatype(char *string);
  extern void plpgsql_adddatum(PLpgSQL_datum * new);
  extern int    plpgsql_add_initdatums(int **varnos);
Index: src/pl/plpgsql/src/scan.l
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/pl/plpgsql/src/scan.l,v
retrieving revision 1.22
diff -c -r1.22 scan.l
*** src/pl/plpgsql/src/scan.l    30 Aug 2002 00:28:41 -0000    1.22
--- src/pl/plpgsql/src/scan.l    9 Sep 2002 04:23:49 -0000
***************
*** 170,183 ****
--- 170,187 ----
  {identifier}{space}*\.{space}*{identifier}{space}*\.{space}*{identifier}    { return plpgsql_parse_tripword(yytext);
}
  {identifier}{space}*%TYPE        { return plpgsql_parse_wordtype(yytext);    }
  {identifier}{space}*\.{space}*{identifier}{space}*%TYPE    { return plpgsql_parse_dblwordtype(yytext); }
+ {identifier}{space}*\.{space}*{identifier}{space}*\.{space}*{identifier}{space}*%TYPE    { return
plpgsql_parse_tripwordtype(yytext);} 
  {identifier}{space}*%ROWTYPE    { return plpgsql_parse_wordrowtype(yytext);    }
+ {identifier}{space}*\.{space}*{identifier}{space}*%ROWTYPE    { return plpgsql_parse_dblwordrowtype(yytext);    }

  \${digit}+                        { return plpgsql_parse_word(yytext);    }
  \${digit}+{space}*\.{space}*{identifier}    { return plpgsql_parse_dblword(yytext);    }
  \${digit}+{space}*\.{space}*{identifier}{space}*\.{space}*{identifier}    { return plpgsql_parse_tripword(yytext); }
  \${digit}+{space}*%TYPE            { return plpgsql_parse_wordtype(yytext);    }
  \${digit}+{space}*\.{space}*{identifier}{space}*%TYPE    { return plpgsql_parse_dblwordtype(yytext); }
+ \${digit}+{space}*\.{space}*{identifier}{space}*\.{space}*{identifier}{space}*%TYPE    { return
plpgsql_parse_tripwordtype(yytext);} 
  \${digit}+{space}*%ROWTYPE        { return plpgsql_parse_wordrowtype(yytext);    }
+ \${digit}+{space}*\.{space}*{identifier}{space}*%ROWTYPE    { return plpgsql_parse_dblwordrowtype(yytext);    }

  {digit}+        { return T_NUMBER;            }

-- nspname.relname.attname%TYPE
DROP FUNCTION t();
CREATE OR REPLACE FUNCTION t() RETURNS TEXT AS '
DECLARE
    col_name pg_catalog.pg_attribute.attname%TYPE;
BEGIN
    col_name := ''uga'';
    RETURN col_name;
END;
' LANGUAGE 'plpgsql';
SELECT t();

-- nspname.relname%ROWTYPE
DROP FUNCTION t();
CREATE OR REPLACE FUNCTION t() RETURNS pg_catalog.pg_attribute AS '
DECLARE
    rec pg_catalog.pg_attribute%ROWTYPE;
BEGIN
    SELECT INTO rec * FROM pg_catalog.pg_attribute WHERE attrelid = 1247 AND attname = ''typname'';
    RETURN rec;
END;
' LANGUAGE 'plpgsql';
SELECT * FROM t();

-- nspname.relname.attname%TYPE
DROP FUNCTION t();
CREATE OR REPLACE FUNCTION t() RETURNS pg_catalog.pg_attribute.attname%TYPE AS '
DECLARE
    rec pg_catalog.pg_attribute.attname%TYPE;
BEGIN
    SELECT INTO rec pg_catalog.pg_attribute.attname FROM pg_catalog.pg_attribute WHERE attrelid = 1247 AND attname =
''typname'';
    RETURN rec;
END;
' LANGUAGE 'plpgsql';
SELECT t();

-- nspname.relname%ROWTYPE
DROP FUNCTION t();
CREATE OR REPLACE FUNCTION t() RETURNS pg_catalog.pg_attribute AS '
DECLARE
    rec pg_catalog.pg_attribute%ROWTYPE;
BEGIN
    SELECT INTO rec * FROM pg_catalog.pg_attribute WHERE attrelid = 1247 AND attname = ''typname'';
    RETURN rec;
END;
' LANGUAGE 'plpgsql';
SELECT * FROM t();