Re: Schemas not available for pl/pgsql %TYPE....

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Joe Conway wrote:
> Tom Lane wrote:
> > Sean Chittenden <sean(at)chittenden(dot)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();

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073