*** a/doc/src/sgml/plpgsql.sgml --- b/doc/src/sgml/plpgsql.sgml *************** *** 321,326 **** --- 321,327 ---- url varchar; myrow tablename%ROWTYPE; myfield tablename.columnname%TYPE; + total quantity%TYPE[]; arow RECORD; *************** *** 584,597 **** ! %TYPE provides the data type of a variable or ! table column. You can use this to declare variables that will hold ! database values. For example, let's say you have a column named ! user_id in your users ! table. To declare a variable with the same data type as ! users.user_id you write: user_id users.user_id%TYPE; --- 585,599 ---- ! %TYPE provides the data type of a variable, ! function argument or table column. You can use this to declare ! variables or arrays that will hold database values. For example, ! let's say you have a column named user_id in ! your users table. To declare a variable with ! the same data type as users.user_id you write: user_id users.user_id%TYPE; + user_id_arr users.user_id%TYPE[]; *************** *** 605,610 **** --- 607,634 ---- + %TYPE copy also array types, even if elements + are row-typed. For example: + + array1 integer[]; -- integers array + copy1 var1%TYPE; -- OK + + array2 users[]; -- array of users + copy2 array2%TYPE; -- OK + + + + + However declaring array of arrays with %TYPE is + not allowed. + + var1 integer[][]; -- OK, integers array + var2 var1%TYPE; -- OK, integers array + var3 var1%TYPE[]; -- ERROR + + + + %TYPE is particularly valuable in polymorphic functions, since the data types needed for internal variables can change from one call to the next. Appropriate variables can be *** a/src/pl/plpgsql/src/gram.y --- b/src/pl/plpgsql/src/gram.y *************** *** 75,81 **** const char *expected, int *endtoken); static PLpgSQL_expr *read_sql_stmt(const char *sqlstart); ! static PLpgSQL_type *read_datatype(int tok); static PLpgSQL_stmt *make_execsql_stmt(int firsttoken, int location); static PLpgSQL_stmt_fetch *read_fetch_direction(void); static void complete_direction(PLpgSQL_stmt_fetch *fetch, --- 75,81 ---- const char *expected, int *endtoken); static PLpgSQL_expr *read_sql_stmt(const char *sqlstart); ! static PLpgSQL_type *read_datatype(int startlocation, int tok); static PLpgSQL_stmt *make_execsql_stmt(int firsttoken, int location); static PLpgSQL_stmt_fetch *read_fetch_direction(void); static void complete_direction(PLpgSQL_stmt_fetch *fetch, *************** *** 162,167 **** --- 162,172 ---- PLpgSQL_diag_item *diagitem; PLpgSQL_stmt_fetch *fetch; PLpgSQL_case_when *casewhen; + struct { + PLpgSQL_word_attribute attr; + bool array; + } typearr; + bool array; } %type decl_sect *************** *** 213,218 **** --- 218,225 ---- %type unreserved_keyword + %type decl_type + %type decl_array /* * Basic non-keyword token types. These are hard-wired into the core lexer. *************** *** 703,716 **** { $$ = true; } ; ! decl_datatype : { ! /* ! * If there's a lookahead token, read_datatype ! * should consume it. ! */ ! $$ = read_datatype(yychar); ! yyclearin; } ; --- 710,795 ---- { $$ = true; } ; ! decl_datatype : T_WORD decl_type { ! if ($2.attr == PLPGSQL_WORD) { ! /* ! * If there's a lookahead token, read_datatype ! * should consume it. ! */ ! $$ = read_datatype(yyloc, yychar); ! yyclearin; ! } ! else { ! if ($2.attr == PLPGSQL_WORD_TYPE) ! $$ = plpgsql_parse_wordtype($1.ident, $2.array); ! else if ($2.attr == PLPGSQL_WORD_ROWTYPE) ! $$ = plpgsql_parse_wordrowtype($1.ident, $2.array); ! else ! elog(FATAL, "wrong value decl_type=%d", $2.attr); ! ! if ($$ == NULL) ! ereport(ERROR, ! (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("can't determine datatype of \"%s\"", $1.ident), ! parser_errposition(@1))); ! } ! } ! | T_CWORD decl_type ! { ! if ($2.attr == PLPGSQL_WORD) { ! /* ! * If there's a lookahead token, read_datatype ! * should consume it. ! */ ! $$ = read_datatype(yyloc, yychar); ! yyclearin; ! } ! else { ! if ($2.attr == PLPGSQL_WORD_TYPE) ! $$ = plpgsql_parse_cwordtype($1.idents, $2.array); ! else if ($2.attr == PLPGSQL_WORD_ROWTYPE) ! $$ = plpgsql_parse_cwordrowtype($1.idents, $2.array); ! else ! elog(FATAL, "wrong value decl_type=%d", $2.attr); ! ! if ($$ == NULL) ! ereport(ERROR, ! (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("can't determine datatype"), ! parser_errposition(@1))); ! } ! } ! ; ! ! decl_type : ! { ! $$.attr = PLPGSQL_WORD; ! $$.array = false; ! } ! | '%' K_TYPE decl_array ! { ! $$.attr = PLPGSQL_WORD_TYPE; ! $$.array = $3; ! } ! | '%' K_ROWTYPE decl_array ! { ! $$.attr = PLPGSQL_WORD_ROWTYPE; ! $$.array = $3; ! } ! ; ! ! decl_arr_elem : '[' ']' ! | '[' ICONST ']' ! ; ! ! decl_array : ! { ! $$ = false; ! } ! | decl_array decl_arr_elem ! { ! $$ = true; } ; *************** *** 2473,2483 **** } static PLpgSQL_type * ! read_datatype(int tok) { StringInfoData ds; char *type_name; - int startlocation; PLpgSQL_type *result; int parenlevel = 0; --- 2552,2561 ---- } static PLpgSQL_type * ! read_datatype(int startlocation, int tok) { StringInfoData ds; char *type_name; PLpgSQL_type *result; int parenlevel = 0; *************** *** 2488,2548 **** if (tok == YYEMPTY) tok = yylex(); - startlocation = yylloc; - - /* - * If we have a simple or composite identifier, check for %TYPE - * and %ROWTYPE constructs. - */ - if (tok == T_WORD) - { - char *dtname = yylval.word.ident; - - tok = yylex(); - if (tok == '%') - { - tok = yylex(); - if (tok_is_keyword(tok, &yylval, - K_TYPE, "type")) - { - result = plpgsql_parse_wordtype(dtname); - if (result) - return result; - } - else if (tok_is_keyword(tok, &yylval, - K_ROWTYPE, "rowtype")) - { - result = plpgsql_parse_wordrowtype(dtname); - if (result) - return result; - } - } - } - else if (tok == T_CWORD) - { - List *dtnames = yylval.cword.idents; - - tok = yylex(); - if (tok == '%') - { - tok = yylex(); - if (tok_is_keyword(tok, &yylval, - K_TYPE, "type")) - { - result = plpgsql_parse_cwordtype(dtnames); - if (result) - return result; - } - else if (tok_is_keyword(tok, &yylval, - K_ROWTYPE, "rowtype")) - { - result = plpgsql_parse_cwordrowtype(dtnames); - if (result) - return result; - } - } - } - while (tok != ';') { if (tok == 0) --- 2566,2571 ---- *************** *** 2584,2589 **** --- 2607,2613 ---- return result; } + static PLpgSQL_stmt * make_execsql_stmt(int firsttoken, int location) { *** a/src/pl/plpgsql/src/pl_comp.c --- b/src/pl/plpgsql/src/pl_comp.c *************** *** 1584,1623 **** /* ---------- ! * plpgsql_parse_wordtype The scanner found word%TYPE. word can be ! * a variable name or a basetype. * ! * Returns datatype struct, or NULL if no match found for word. * ---------- */ ! PLpgSQL_type * ! plpgsql_parse_wordtype(char *ident) ! { ! PLpgSQL_type *dtype; ! PLpgSQL_nsitem *nse; ! HeapTuple typeTup; /* * Do a lookup in the current namespace stack */ nse = plpgsql_ns_lookup(plpgsql_ns_top(), false, ident, NULL, NULL, NULL); ! if (nse != NULL) ! { ! switch (nse->itemtype) ! { ! case PLPGSQL_NSTYPE_VAR: ! return ((PLpgSQL_var *) (plpgsql_Datums[nse->itemno]))->datatype; ! /* XXX perhaps allow REC/ROW here? */ ! default: ! return NULL; ! } } /* * Word wasn't found in the namespace stack. Try to find a data type with * that name, but ignore shell types and complex types. --- 1584,1874 ---- /* ---------- ! * build_arraydatatype Try to construct array type from a type * ! * Function is used internally by plpgsql_parse_wordtype and ! * plpgsql_parse_cwordtype when syntax word%TYPE[] is handled. ! * ! * Returns datatype struct, or NULL if type can't be used. * ---------- */ ! static PLpgSQL_type * ! build_arraydatatype(Oid typoid, int32 typmod, Oid collation) { ! Oid elem_typoid; + if (type_is_array(typoid)) + /* don't allow to construct array of array */ + return NULL; + else + { + /* get array type for given scalar type */ + elem_typoid = get_array_type(typoid); + if (elem_typoid != InvalidOid) + return plpgsql_build_datatype(elem_typoid, typmod, collation); + else + /* typoid can't be used as a datatype of array */ + return NULL; + } + } + + /* ---------- + * plpgsql_get_variable1 Returns variable from current stack + * + * Parameter type is used to early filter variables + * Used internally by plpgsql_parse_XXX. + */ + static PLpgSQL_datum* + plpgsql_get_variable1(const char* ident, int type, bool* found) { + PLpgSQL_nsitem *nse; + /* * Do a lookup in the current namespace stack */ nse = plpgsql_ns_lookup(plpgsql_ns_top(), false, ident, NULL, NULL, NULL); + if (nse) { + if (found) + *found = true; ! if (type != -1 && type != nse->itemtype) ! /* variable type do not match */ ! return NULL; ! else ! return plpgsql_Datums[nse->itemno]; ! } ! else { ! if (found) ! *found = false; ! return NULL; ! } ! } ! ! /* ---------- ! * plpgsql_get_variable2 Returns variable from current stack ! * ! * Parameter type is used to early filter variables ! * Used internally by plpgsql_parse_XXX. ! */ ! static PLpgSQL_datum* ! plpgsql_get_variable2(const char* block_label, const char* ident, int type, bool* found) { ! PLpgSQL_nsitem *nse; ! int k; ! ! /* ! * Do a lookup in the current namespace stack ! */ ! nse = plpgsql_ns_lookup(plpgsql_ns_top(), false, ! block_label, ident, NULL, ! &k); ! ! if (nse && k == 2) { ! if (found) ! *found = true; ! ! if (type != -1 && type != nse->itemtype) ! /* variable type do not match */ ! return NULL; ! else ! return plpgsql_Datums[nse->itemno]; ! } ! else { ! if (found) ! *found = false; ! ! return NULL; } + } + + + + /* ---------- + * plpgsql_get_field_reloid Returns type Oid of field from relation + * + * Used internally in plpgsql_parse_XXX + * + */ + static Oid + plpgsql_get_field_reloid(Oid classOid, const char* fldname, int32* typmod, Oid* typcoll) { + HeapTuple classtup = NULL; + HeapTuple attrtup = NULL; + HeapTuple typetup = NULL; + Form_pg_class classStruct; + Form_pg_attribute attrStruct; + Oid typoid = InvalidOid; + MemoryContext oldCxt; + + if (typmod) + *typmod = -1; + if (typcoll) + *typcoll = plpgsql_curr_compile->fn_input_collation; + + /* Avoid memory leaks in the long-term function context */ + oldCxt = MemoryContextSwitchTo(compile_tmp_cxt); + + /* + * Fetch class info + */ + classtup = SearchSysCache1(RELOID, ObjectIdGetDatum(classOid)); + if (!HeapTupleIsValid(classtup)) + goto done; + classStruct = (Form_pg_class) GETSTRUCT(classtup); + + /* + * It must be a relation, sequence, view, or type + */ + if (classStruct->relkind != RELKIND_RELATION && + classStruct->relkind != RELKIND_SEQUENCE && + classStruct->relkind != RELKIND_VIEW && + classStruct->relkind != RELKIND_COMPOSITE_TYPE) + goto done; + + /* + * Fetch the named table field and its type + */ + attrtup = SearchSysCacheAttName(classOid, fldname); + if (!HeapTupleIsValid(attrtup)) + goto done; + attrStruct = (Form_pg_attribute) GETSTRUCT(attrtup); + + typoid = attrStruct->atttypid; + if (typmod) + *typmod = attrStruct->atttypmod; + if (typcoll) + *typcoll = attrStruct->attcollation; + + done: + if (HeapTupleIsValid(classtup)) + ReleaseSysCache(classtup); + if (HeapTupleIsValid(attrtup)) + ReleaseSysCache(attrtup); + if (HeapTupleIsValid(typetup)) + ReleaseSysCache(typetup); + + MemoryContextSwitchTo(oldCxt); + return typoid; + } + + + /* ---------- + * plpgsql_get_field_typoid Returns type Oid of field from composite type + * + * Used internally in plpgsql_parse_XXX + * + */ + static Oid + plpgsql_get_field_typoid(Oid comp_typoid, const char* fldname, int32* typmod, Oid* typcoll) { + + return plpgsql_get_field_reloid( + get_typ_typrelid(comp_typoid), + fldname, + typmod, + typcoll); + } + + /* ---------- + * plpgsql_get_field2_typoid Returns type Oid of field1.field2 from composite type + * + * Used internally in plpgsql_parse_XXX + * + */ + static Oid + plpgsql_get_field2_typoid( + Oid comp_typoid, + const char* fldname1, + const char* fldname2, + int32* typmod, + Oid* typcoll) + { + Oid typoid; + typoid = plpgsql_get_field_reloid( + get_typ_typrelid(comp_typoid), + fldname1, + NULL, + NULL); + + if (type_is_rowtype(typoid)) + return plpgsql_get_field_reloid( + get_typ_typrelid(typoid), + fldname2, + typmod, + typcoll); + else + return InvalidOid; + } + + + /* ---------- + * plpgsql_get_field2_reloid Returns type Oid of field1.field2 from relation + * + * Used internally in plpgsql_parse_XXX + * + */ + static Oid + plpgsql_get_field2_reloid( + Oid relid, + const char* fldname1, + const char* fldname2, + int32* typmod, + Oid* typcoll) + { + Oid typoid; + typoid = plpgsql_get_field_reloid( + relid, + fldname1, + NULL, + NULL); + + if (type_is_rowtype(typoid)) + return plpgsql_get_field_reloid( + get_typ_typrelid(typoid), + fldname2, + typmod, + typcoll); + else + return InvalidOid; + } + + + /* ---------- + * plpgsql_parse_wordtype The scanner found word%TYPE or word%TYPE[]. + * word can be a variable name or a basetype. + * + * Returns datatype struct, or NULL if no match found for word. + * ---------- + */ + PLpgSQL_type * + plpgsql_parse_wordtype(char *ident, bool array) + { + PLpgSQL_type* dtype; + PLpgSQL_var *var; + bool exists; + HeapTuple typeTup; + + /* + * Do a lookup in the current namespace stack + */ + var = (PLpgSQL_var*) plpgsql_get_variable1( + ident, + PLPGSQL_NSTYPE_VAR, + &exists); + if (var) { + /* found scalar variable */ + if (array) + return build_arraydatatype( + var->datatype->typoid, + -1, + plpgsql_curr_compile->fn_input_collation); + else + return var->datatype; + } + else if (exists) + /* non-scalar variable found */ + return NULL; + /* * Word wasn't found in the namespace stack. Try to find a data type with * that name, but ignore shell types and complex types. *************** *** 1634,1640 **** return NULL; } ! dtype = build_datatype(typeTup, -1, plpgsql_curr_compile->fn_input_collation); ReleaseSysCache(typeTup); --- 1885,1895 ---- return NULL; } ! if (array) ! dtype = build_datatype(typeTup, -1, ! plpgsql_curr_compile->fn_input_collation); ! else ! dtype = build_arraydatatype(HeapTupleGetOid(typeTup), -1, plpgsql_curr_compile->fn_input_collation); ReleaseSysCache(typeTup); *************** *** 1650,1777 **** /* ---------- ! * plpgsql_parse_cwordtype Same lookup for compositeword%TYPE * ---------- */ PLpgSQL_type * ! plpgsql_parse_cwordtype(List *idents) { PLpgSQL_type *dtype = NULL; ! PLpgSQL_nsitem *nse; ! const char *fldname; ! Oid classOid; ! HeapTuple classtup = NULL; ! HeapTuple attrtup = NULL; ! HeapTuple typetup = NULL; ! Form_pg_class classStruct; ! Form_pg_attribute attrStruct; MemoryContext oldCxt; /* Avoid memory leaks in the long-term function context */ oldCxt = MemoryContextSwitchTo(compile_tmp_cxt); if (list_length(idents) == 2) { /* ! * Do a lookup in the current namespace stack. We don't need to check * number of names matched, because we will only consider scalar * variables. */ ! nse = plpgsql_ns_lookup(plpgsql_ns_top(), false, ! strVal(linitial(idents)), ! strVal(lsecond(idents)), ! NULL, NULL); ! if (nse != NULL && nse->itemtype == PLPGSQL_NSTYPE_VAR) ! { ! dtype = ((PLpgSQL_var *) (plpgsql_Datums[nse->itemno]))->datatype; ! goto done; } /* ! * First word could also be a table name */ ! classOid = RelnameGetRelid(strVal(linitial(idents))); ! if (!OidIsValid(classOid)) ! goto done; ! fldname = strVal(lsecond(idents)); } else if (list_length(idents) == 3) { RangeVar *relvar; ! relvar = makeRangeVar(strVal(linitial(idents)), ! strVal(lsecond(idents)), ! -1); ! /* Can't lock relation - we might not have privileges. */ ! classOid = RangeVarGetRelid(relvar, NoLock, true, false); ! if (!OidIsValid(classOid)) ! goto done; ! fldname = strVal(lthird(idents)); ! } ! else ! goto done; ! classtup = SearchSysCache1(RELOID, ObjectIdGetDatum(classOid)); ! if (!HeapTupleIsValid(classtup)) ! goto done; ! classStruct = (Form_pg_class) GETSTRUCT(classtup); ! /* ! * It must be a relation, sequence, view, or type ! */ ! if (classStruct->relkind != RELKIND_RELATION && ! classStruct->relkind != RELKIND_SEQUENCE && ! classStruct->relkind != RELKIND_VIEW && ! classStruct->relkind != RELKIND_COMPOSITE_TYPE) ! goto done; ! /* ! * Fetch the named table field and its type ! */ ! attrtup = SearchSysCacheAttName(classOid, fldname); ! if (!HeapTupleIsValid(attrtup)) ! goto done; ! attrStruct = (Form_pg_attribute) GETSTRUCT(attrtup); ! typetup = SearchSysCache1(TYPEOID, ! ObjectIdGetDatum(attrStruct->atttypid)); ! if (!HeapTupleIsValid(typetup)) ! elog(ERROR, "cache lookup failed for type %u", attrStruct->atttypid); ! /* ! * Found that - build a compiler type struct in the caller's cxt and ! * return it ! */ MemoryContextSwitchTo(oldCxt); - dtype = build_datatype(typetup, - attrStruct->atttypmod, - attrStruct->attcollation); - MemoryContextSwitchTo(compile_tmp_cxt); ! done: ! if (HeapTupleIsValid(classtup)) ! ReleaseSysCache(classtup); ! if (HeapTupleIsValid(attrtup)) ! ReleaseSysCache(attrtup); ! if (HeapTupleIsValid(typetup)) ! ReleaseSysCache(typetup); MemoryContextSwitchTo(oldCxt); return dtype; } /* ---------- ! * plpgsql_parse_wordrowtype Scanner found word%ROWTYPE. ! * So word must be a table name. * ---------- */ PLpgSQL_type * ! plpgsql_parse_wordrowtype(char *ident) { Oid classOid; /* Lookup the relation */ classOid = RelnameGetRelid(ident); if (!OidIsValid(classOid)) --- 1905,2145 ---- /* ---------- ! * plpgsql_parse_cwordtype Same lookup for compositeword%TYPE or %TYPE[] * ---------- */ PLpgSQL_type * ! plpgsql_parse_cwordtype(List *idents, bool array) { PLpgSQL_type *dtype = NULL; ! PLpgSQL_row *row; ! PLpgSQL_var *var; MemoryContext oldCxt; + Oid relid; + Oid typoid; + int32 typmod; + Oid typcoll; + + #define invalidate_type_info \ + typoid = InvalidOid; \ + typmod = -1; \ + typcoll = plpgsql_curr_compile->fn_input_collation; + + #define word1 strVal(linitial(idents)) + #define word2 strVal(lsecond(idents)) + #define word3 strVal(lthird(idents)) + + invalidate_type_info; + /* Avoid memory leaks in the long-term function context */ oldCxt = MemoryContextSwitchTo(compile_tmp_cxt); if (list_length(idents) == 2) { /* ! * case 1: block_label.scalar_variable ! */ ! ! /* Do a lookup in the current namespace stack. We don't need to check * number of names matched, because we will only consider scalar * variables. */ ! var = (PLpgSQL_var *) plpgsql_get_variable2( ! word1, ! word2, ! PLPGSQL_NSTYPE_VAR, ! NULL); ! if (var) { ! dtype = var->datatype; ! if (!type_is_rowtype(dtype->typoid)) { ! if (array) { ! typoid = dtype->typoid; ! goto have_typoid; ! } ! else ! goto have_dtype; ! } ! } ! ! /* ! * case 2: row_variable.scalar_field ! */ ! row = (PLpgSQL_row *)plpgsql_get_variable1( ! word1, ! PLPGSQL_NSTYPE_ROW, NULL); ! if (row && row->rowtupdesc) { ! typoid = plpgsql_get_field_typoid( ! row->rowtupdesc->tdtypeid, ! word2, ! &typmod, ! &typcoll); ! ! if (!type_is_rowtype(typoid)) ! goto have_typoid; ! ! invalidate_type_info; } /* ! * case 3: First word could also be a table name */ ! relid = RelnameGetRelid(word1); ! typoid = plpgsql_get_field_reloid(relid, word2, &typmod, &typcoll); ! ! if (type_is_rowtype(typoid)) { ! /* field is composite type, mark as invalid */ ! invalidate_type_info; ! } ! ! goto have_typoid; } else if (list_length(idents) == 3) { RangeVar *relvar; ! /* ! * case 1: row_variable.composite_type.scalar_field ! */ ! row = (PLpgSQL_row *)plpgsql_get_variable1( ! word1, ! PLPGSQL_NSTYPE_ROW, ! NULL); ! if (row && row->rowtupdesc) { ! typoid = plpgsql_get_field2_typoid( ! row->rowtupdesc->tdtypeid, ! word2, word3, ! &typmod, &typcoll); ! if (!type_is_rowtype(typoid)) ! goto have_typoid; ! invalidate_type_info; ! } ! /* ! * case 2: block_label.row_variable.scalar_field ! */ ! row = (PLpgSQL_row *)plpgsql_get_variable2( ! word1, ! word2, ! PLPGSQL_NSTYPE_ROW, ! NULL); ! if (row && row->rowtupdesc) { ! typoid = plpgsql_get_field_typoid( ! row->rowtupdesc->tdtypeid, ! word3, ! &typmod, ! &typcoll); ! ! if (!type_is_rowtype(typoid)) ! goto have_typoid; ! ! invalidate_type_info; ! } ! ! /* ! * case 3: relation.row_field.scalar_field ! */ ! relid = RelnameGetRelid(word1); ! if (OidIsValid(relid)) { ! typoid = plpgsql_get_field2_reloid( ! relid, ! word2, ! word3, ! &typmod, ! &typcoll); ! ! if (!type_is_rowtype(typoid)) ! goto have_typoid; ! ! invalidate_type_info; ! } ! ! /* ! * case 4: schema.relation.scalar_field ! */ ! relvar = makeRangeVar(word1, word2, -1); ! /* Can't lock relation - we might not have privileges. */ ! relid = RangeVarGetRelid(relvar, NoLock, true, false); ! typoid = plpgsql_get_field_reloid( ! relid, ! word3, ! &typmod, ! &typcoll); ! ! if (type_is_rowtype(typoid)) { ! invalidate_type_info; ! } ! ! goto have_typoid; ! } ! ! have_typoid: MemoryContextSwitchTo(oldCxt); ! if (OidIsValid(typoid)) { ! if (array) ! return build_arraydatatype(typoid, typmod, typcoll); ! else ! return plpgsql_build_datatype(typoid, typmod, typcoll); ! } ! else ! return NULL; + have_dtype: MemoryContextSwitchTo(oldCxt); return dtype; + + #undef invalidate_type_info + #undef word1 + #undef word2 + #undef word3 } /* ---------- ! * plpgsql_parse_wordrowtype Scanner found word%ROWTYPE or word%ROWTYPE[]. ! * So word must be a table name or parameter/variable of record type. * ---------- */ PLpgSQL_type * ! plpgsql_parse_wordrowtype(char *ident, bool array) { + PLpgSQL_row *row; + bool exists; Oid classOid; + /* + * Do a lookup in the current namespace stack + */ + row = (PLpgSQL_row *)plpgsql_get_variable1( + ident, + PLPGSQL_NSTYPE_ROW, + &exists); + + if (row && row->rowtupdesc) { + /* row variable */ + TupleDesc tuple = row->rowtupdesc; + + if (array) + return build_arraydatatype( + tuple->tdtypeid, + tuple->tdtypmod, + plpgsql_curr_compile->fn_input_collation); + else + return plpgsql_build_datatype( + tuple->tdtypeid, + tuple->tdtypmod, + plpgsql_curr_compile->fn_input_collation); + } + else if (exists) + /* scalar variable found */ + return NULL; + /* Lookup the relation */ classOid = RelnameGetRelid(ident); if (!OidIsValid(classOid)) *************** *** 1780,1816 **** errmsg("relation \"%s\" does not exist", ident))); /* Build and return the row type struct */ ! return plpgsql_build_datatype(get_rel_type_id(classOid), -1, InvalidOid); } /* ---------- ! * plpgsql_parse_cwordrowtype Scanner found compositeword%ROWTYPE. ! * So word must be a namespace qualified table name. * ---------- */ PLpgSQL_type * ! plpgsql_parse_cwordrowtype(List *idents) { ! Oid classOid; RangeVar *relvar; MemoryContext oldCxt; ! if (list_length(idents) != 2) ! return NULL; /* Avoid memory leaks in long-term function context */ oldCxt = MemoryContextSwitchTo(compile_tmp_cxt); ! /* Look up relation name. Can't lock it - we might not have privileges. */ ! relvar = makeRangeVar(strVal(linitial(idents)), ! strVal(lsecond(idents)), ! -1); ! classOid = RangeVarGetRelid(relvar, NoLock, false, false); MemoryContextSwitchTo(oldCxt); ! /* Build and return the row type struct */ ! return plpgsql_build_datatype(get_rel_type_id(classOid), -1, InvalidOid); } /* --- 2148,2348 ---- errmsg("relation \"%s\" does not exist", ident))); /* Build and return the row type struct */ ! if (array) ! return build_arraydatatype(get_rel_type_id(classOid), -1, InvalidOid); ! else ! return plpgsql_build_datatype(get_rel_type_id(classOid), -1, InvalidOid); } /* ---------- ! * plpgsql_parse_cwordrowtype Scanner found compositeword%ROWTYPE or %ROWTYPE[]. * ---------- */ PLpgSQL_type * ! plpgsql_parse_cwordrowtype(List *idents, bool array) { ! PLpgSQL_row* row; RangeVar *relvar; MemoryContext oldCxt; + + Oid relid; + Oid typoid; + int32 typmod; + Oid typcoll; ! #define invalidate_type_info \ ! typoid = InvalidOid; \ ! typmod = -1; \ ! typcoll = plpgsql_curr_compile->fn_input_collation; ! ! #define word1 strVal(linitial(idents)) ! #define word2 strVal(lsecond(idents)) ! #define word3 strVal(lthird(idents)) ! ! invalidate_type_info; /* Avoid memory leaks in long-term function context */ oldCxt = MemoryContextSwitchTo(compile_tmp_cxt); ! if (list_length(idents) == 2) { ! PLpgSQL_row* row; ! /* ! * case 1: row_variable.row_field ! */ ! row = (PLpgSQL_row *)plpgsql_get_variable1( ! word1, ! PLPGSQL_NSTYPE_ROW, ! NULL); ! if (row && row->rowtupdesc) { ! typoid = plpgsql_get_field_typoid( ! row->rowtupdesc->tdtypeid, ! word2, ! &typmod, ! &typcoll); ! ! if (type_is_rowtype(typoid)) ! goto done; ! ! invalidate_type_info; ! } ! ! /* ! * case 2: block_label.row_variable ! */ ! row = (PLpgSQL_row *)plpgsql_get_variable2( ! word1, ! word2, ! PLPGSQL_NSTYPE_ROW, ! NULL); ! if (row && row->rowtupdesc) { ! typoid = row->rowtupdesc->tdtypeid; ! typmod = row->rowtupdesc->tdtypmod; ! goto done; ! } ! ! /* ! * case 3: relation.row_field ! * ! */ ! relid = RelnameGetRelid(strVal(linitial(idents))); ! if (OidIsValid(relid)) { ! typoid = plpgsql_get_field_reloid( ! relid, ! word2, ! &typmod, ! &typcoll); ! ! if (type_is_rowtype(typoid)) ! goto done; ! ! invalidate_type_info; ! } ! ! /* ! * case 4: schema.relation ! */ ! /* Look up relation name. Can't lock it - we might not have privileges. */ ! relvar = makeRangeVar(word1, word2, -1); ! relid = RangeVarGetRelid(relvar, NoLock, false, false); ! typoid = get_rel_type_id(relid); ! goto done; ! } ! else if (list_length(idents) == 3) { ! /* ! * case 1: row_variable.composite_type.field ! */ ! row = (PLpgSQL_row *)plpgsql_get_variable1( ! word1, ! PLPGSQL_NSTYPE_ROW, ! NULL); ! if (row && row->rowtupdesc) { ! typoid = plpgsql_get_field2_typoid( ! row->rowtupdesc->tdtypeid, ! word2, ! word3, ! &typmod, ! &typcoll); ! ! if (type_is_rowtype(typoid)) ! goto done; ! ! invalidate_type_info; ! } ! ! /* ! * case 2: block_label.row_variable.row_field ! */ ! row = (PLpgSQL_row *)plpgsql_get_variable2( ! word1, ! word2, ! PLPGSQL_NSTYPE_ROW, ! NULL); ! ! if (row && row->rowtupdesc) { ! typoid = plpgsql_get_field_typoid( ! row->rowtupdesc->tdtypeid, ! word3, ! &typmod, ! &typcoll); ! ! if (type_is_rowtype(typoid)) ! goto done; ! ! invalidate_type_info; ! } ! ! /* ! * case 3: composite_type.row_field.row_field ! */ ! relid = RelnameGetRelid(word1); ! if (OidIsValid(relid)) { ! typoid = plpgsql_get_field2_reloid( ! relid, ! word2, ! word3, ! &typmod, ! &typcoll); ! ! if (type_is_rowtype(typoid)) ! goto done; ! ! invalidate_type_info; ! } + /* + * case 4: schema.relation.row_field + */ + relvar = makeRangeVar(word1, word2, -1); + relid = RangeVarGetRelid(relvar, NoLock, false, false); + if (OidIsValid(relid)) { + typoid = plpgsql_get_field_reloid( + relid, + word3, + &typmod, + &typcoll); + } + + if (!type_is_rowtype(typoid)) { + invalidate_type_info; + } + } + + done: MemoryContextSwitchTo(oldCxt); ! if (OidIsValid(typoid)) { ! if (array) ! return build_arraydatatype(typoid, typmod, typcoll); ! else ! return plpgsql_build_datatype(typoid, typmod, typcoll); ! } ! else ! return NULL; ! ! #undef invalidate_type_info ! #undef word1 ! #undef word2 ! #undef word3 } /* *** a/src/pl/plpgsql/src/plpgsql.h --- b/src/pl/plpgsql/src/plpgsql.h *************** *** 153,158 **** --- 153,168 ---- PLPGSQL_RESOLVE_COLUMN /* prefer table column to plpgsql var */ } PLpgSQL_resolve_option; + /* -------- + * Information about %TYPE|%ROWTYPE attribute + * in a data type declaration (rule decl_datatype) + * -------- + */ + typedef enum { + PLPGSQL_WORD, /* word - T_WORD or T_CWORD */ + PLPGSQL_WORD_TYPE, /* word%TYPE */ + PLPGSQL_WORD_ROWTYPE /* word%ROWTYPE */ + } PLpgSQL_word_attribute; /********************************************************************** * Node and structure definitions *************** *** 877,886 **** PLwdatum *wdatum, PLcword *cword); extern bool plpgsql_parse_tripword(char *word1, char *word2, char *word3, PLwdatum *wdatum, PLcword *cword); ! extern PLpgSQL_type *plpgsql_parse_wordtype(char *ident); ! extern PLpgSQL_type *plpgsql_parse_cwordtype(List *idents); ! extern PLpgSQL_type *plpgsql_parse_wordrowtype(char *ident); ! extern PLpgSQL_type *plpgsql_parse_cwordrowtype(List *idents); extern PLpgSQL_type *plpgsql_build_datatype(Oid typeOid, int32 typmod, Oid collation); extern PLpgSQL_variable *plpgsql_build_variable(const char *refname, int lineno, --- 887,896 ---- PLwdatum *wdatum, PLcword *cword); extern bool plpgsql_parse_tripword(char *word1, char *word2, char *word3, PLwdatum *wdatum, PLcword *cword); ! extern PLpgSQL_type *plpgsql_parse_wordtype(char *ident, bool array); ! extern PLpgSQL_type *plpgsql_parse_cwordtype(List *idents, bool array); ! extern PLpgSQL_type *plpgsql_parse_wordrowtype(char *ident, bool array); ! extern PLpgSQL_type *plpgsql_parse_cwordrowtype(List *idents, bool array); extern PLpgSQL_type *plpgsql_build_datatype(Oid typeOid, int32 typmod, Oid collation); extern PLpgSQL_variable *plpgsql_build_variable(const char *refname, int lineno, *** a/src/test/regress/expected/plpgsql.out --- b/src/test/regress/expected/plpgsql.out *************** *** 4315,4320 **** --- 4315,5111 ---- (1 row) drop function unreserved_test(); + -- + -- Test attributes %TYPE and %ROWTYPE + -- and also declaring arrays from copied types. + -- + create type field_type2 as ( + scalar_field text + ); + create type field_type as ( + scalar_field boolean, + comp_field field_type2 + ); + create type comp_type as ( + scalar_field text, + comp_field field_type + ); + create schema test_plpgsql; + create type test_plpgsql.field_type as ( + field boolean + ); + create type test_plpgsql.comp_type as ( + scalar_field integer, + comp_field test_plpgsql.field_type + ); + create function test_type_and_rowtype_attribute(param1 integer, param2 comp_type) + returns void as $$ + declare + -- scalar types + var1 boolean; + var2 integer; + var3 varchar(5); + + -- record type + rec1 comp_type%ROWTYPE; + + -- copy types + copy_var1 var1%TYPE; + copy_var2 var2%TYPE; + copy_var3 var3%TYPE; + copy_rec1 rec1%ROWTYPE; + copy_param1 param1%TYPE; + copy_param2 param2%ROWTYPE; + copy_field comp_type.scalar_field%TYPE; + + -- declare arrays of copied types + -- many [] or [NUM] can be used, but all are folded during type construction + arr_var1 var1%TYPE[]; + arr_var2 var2%TYPE[1][2][3]; + arr_var3 var3%TYPE[][]; + arr_rec1 rec1%ROWTYPE[]; + arr_rec2 comp_type%ROWTYPE[]; + arr_param1 param1%TYPE[]; + arr_param2 param2%ROWTYPE[][][][][]; + arr_field comp_type.scalar_field%TYPE[][]; + begin + -- finally check types + raise notice '%', pg_typeof(copy_var1); + raise notice '%', pg_typeof(copy_var2); + raise notice '%', pg_typeof(copy_var3); + raise notice '%', pg_typeof(copy_rec1); + raise notice '%', pg_typeof(copy_param1); + raise notice '%', pg_typeof(copy_param2); + raise notice '%', pg_typeof(copy_field); + + raise notice '%', pg_typeof(arr_var1); + raise notice '%', pg_typeof(arr_var2); + raise notice '%', pg_typeof(arr_var3); + raise notice '%', pg_typeof(arr_rec1); + raise notice '%', pg_typeof(arr_param1); + raise notice '%', pg_typeof(arr_param2); + raise notice '%', pg_typeof(arr_field); + end + $$ language plpgsql; + SELECT test_type_and_rowtype_attribute(NULL, NULL); + NOTICE: boolean + NOTICE: integer + NOTICE: character varying + NOTICE: comp_type + NOTICE: integer + NOTICE: comp_type + NOTICE: text + NOTICE: boolean[] + NOTICE: integer[] + NOTICE: character varying[] + NOTICE: comp_type[] + NOTICE: integer[] + NOTICE: comp_type[] + NOTICE: text[] + test_type_and_rowtype_attribute + --------------------------------- + + (1 row) + + -- compilation should fail, because we don't allow + -- to declare array of arrays + create function test_type_attribute_array_of_array() + returns void as $$ + declare + arr integer[]; + arrarr arr%TYPE[]; -- <= error + begin + null; + end; + $$ language plpgsql; + ERROR: can't determine datatype of "arr" + LINE 5: arrarr arr%TYPE[]; -- <= error + ^ + -- compilation should fail, because we don't allow + -- to declare array of arrays + create or replace function test_rowtype_attribute_array_of_array() + returns void as $$ + declare + arr comp_type%ROWTYPE[]; + arr1 arr%TYPE; -- <= ok, type of 'arr' is comp_type[] + arrarr arr%ROWTYPE[]; -- <= error + begin + null; + end; + $$ language plpgsql; + ERROR: can't determine datatype of "arr" + LINE 6: arrarr arr%ROWTYPE[]; -- <= error + ^ + -- + -- Tests syntax: word %TYPE/%ROWTYPE [] + -- + -- test variable%TYPE + create or replace function test_declare1_1() + returns void + language plpgsql + as $$ + declare + var1 integer; + var2 var1%TYPE; + var3 var2%TYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + end; + $$; + select test_declare1_1(); + NOTICE: var1 - integer + NOTICE: var2 - integer + NOTICE: var3 - integer[] + test_declare1_1 + ----------------- + + (1 row) + + -- test scalar_variable%ROWTYPE - ERROR + create or replace function test_declare1_2() + returns void + language plpgsql + as $$ + declare + var1 integer; + var2 var1%ROWTYPE; + begin + null; + end; + $$; + ERROR: can't determine datatype of "var1" + LINE 7: var2 var1%ROWTYPE; + ^ + -- test record_variable%ROWTYPE - ERROR + create or replace function test_declare1_3() + returns void + language plpgsql + as $$ + declare + var1 record; + var2 var1%ROWTYPE; + begin + null; + end; + $$; + ERROR: can't determine datatype of "var1" + LINE 7: var2 var1%ROWTYPE; + ^ + -- test relation%ROWTYPE - OK + create or replace function test_declare1_4() + returns void + language plpgsql + as $$ + declare + var1 comp_type; + var2 comp_type%ROWTYPE; + var3 comp_type%ROWTYPE[]; + var4 var2%ROWTYPE; + var5 var2%ROWTYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + raise notice 'var4 - %', pg_typeof(var4); + raise notice 'var5 - %', pg_typeof(var5); + end; + $$; + select test_declare1_4(); + NOTICE: var1 - comp_type + NOTICE: var2 - comp_type + NOTICE: var3 - comp_type[] + NOTICE: var4 - comp_type + NOTICE: var5 - comp_type[] + test_declare1_4 + ----------------- + + (1 row) + + -- + -- Tests syntax: word1.word2 %TYPE/%ROWTYPE [] + -- + -- test: block_label.scalar_variable %TYPE + create or replace function test_declare2_1() + returns void + language plpgsql + as $$ + <> + declare + var1 integer; + begin + declare + var2 block.var1%TYPE; + var3 block.var1%TYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + end; + end; + $$; + select test_declare2_1(); + NOTICE: var1 - integer + NOTICE: var2 - integer + NOTICE: var3 - integer[] + test_declare2_1 + ----------------- + + (1 row) + + -- test: block_label.scalar_variable %ROWTYPE - ERROR + create or replace function test_declare2_2() + returns void + language plpgsql + as $$ + <> + declare + var1 integer; + begin + declare + var2 block.var1%ROWTYPE; + begin + end; + end; + $$; + ERROR: schema "block" does not exist + CONTEXT: compilation of PL/pgSQL function "test_declare2_2" near line 7 + -- test: block_label.row_variable %TYPE - ERROR + create or replace function test_declare2_3() + returns void + language plpgsql + as $$ + <> + declare + var1 comp_type; + begin + declare + var2 block.var1%TYPE; + begin + end; + end; + $$; + ERROR: can't determine datatype + LINE 10: var2 block.var1%TYPE; + ^ + -- test: block_label.row_variable %ROWTYPE + create or replace function test_declare2_4() + returns void + language plpgsql + as $$ + <> + declare + var1 comp_type; + begin + declare + var2 block.var1%ROWTYPE; + var3 block.var1%ROWTYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + end; + end; + $$; + select test_declare2_4(); + NOTICE: var1 - comp_type + NOTICE: var2 - comp_type + NOTICE: var3 - comp_type[] + test_declare2_4 + ----------------- + + (1 row) + + -- test: row_variable.scalar_field % TYPE + create or replace function test_declare2_5() + returns void + language plpgsql + as $$ + declare + var1 comp_type; + var2 var1.scalar_field%TYPE; + var3 var1.scalar_field%TYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + end; + $$; + select test_declare2_5(); + NOTICE: var1 - comp_type + NOTICE: var2 - text + NOTICE: var3 - text[] + test_declare2_5 + ----------------- + + (1 row) + + -- test: row_variable.scalar_field % ROWTYPE - ERROR + create or replace function test_declare2_6() + returns void + language plpgsql + as $$ + declare + var1 comp_type; + var2 var1.scalar_field%ROWTYPE; + begin + end; + $$; + ERROR: schema "var1" does not exist + CONTEXT: compilation of PL/pgSQL function "test_declare2_6" near line 4 + -- test: row_variable.row_field % TYPE - ERROR + create or replace function test_declare2_7() + returns void + language plpgsql + as $$ + declare + var1 comp_type; + var2 var1.comp_field%TYPE; + begin + end; + $$; + ERROR: can't determine datatype + LINE 7: var2 var1.comp_field%TYPE; + ^ + -- test: row_variable.row_field % ROWTYPE + create or replace function test_declare2_8() + returns void + language plpgsql + as $$ + declare + var1 comp_type; + var2 var1.comp_field%ROWTYPE; + var3 var1.comp_field%ROWTYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + end; + $$; + select test_declare2_8(); + NOTICE: var1 - comp_type + NOTICE: var2 - field_type + NOTICE: var3 - field_type[] + test_declare2_8 + ----------------- + + (1 row) + + -- test: relation.scalar_field % TYPE + create or replace function test_declare2_9() + returns void + language plpgsql + as $$ + declare + var1 comp_type.scalar_field%TYPE; + var2 comp_type.scalar_field%TYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + end; + $$; + select test_declare2_9(); + NOTICE: var1 - text + NOTICE: var2 - text[] + test_declare2_9 + ----------------- + + (1 row) + + -- test: relation.scalar_field % ROWTYPE - ERROR + create or replace function test_declare2_9() + returns void + language plpgsql + as $$ + declare + var1 comp_type.scalar_field%ROWTYPE; + begin + end; + $$; + ERROR: schema "comp_type" does not exist + CONTEXT: compilation of PL/pgSQL function "test_declare2_9" near line 3 + -- test: relation.row_field % TYPE - ERROR + create or replace function test_declare2_10() + returns void + language plpgsql + as $$ + declare + var1 comp_type.comp_field%TYPE; + begin + end; + $$; + ERROR: can't determine datatype + LINE 6: var1 comp_type.comp_field%TYPE; + ^ + -- test: relation.row_field % ROWTYPE + create or replace function test_declare2_11() + returns void + language plpgsql + as $$ + declare + var1 comp_type.comp_field%ROWTYPE; + var2 comp_type.comp_field%ROWTYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + end; + $$; + select test_declare2_11(); + NOTICE: var1 - field_type + NOTICE: var2 - field_type[] + test_declare2_11 + ------------------ + + (1 row) + + -- test: schama.relation % TYPE - ERROR + create or replace function test_declare2_12() + returns void + language plpgsql + as $$ + declare + var1 test_plpgsql.comp_type%TYPE; + begin + end; + $$; + ERROR: can't determine datatype + LINE 6: var1 test_plpgsql.comp_type%TYPE; + ^ + -- test: schama.relation % ROWTYPE + create or replace function test_declare2_13() + returns void + language plpgsql + as $$ + declare + var1 test_plpgsql.comp_type%ROWTYPE; + var2 test_plpgsql.comp_type%ROWTYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + end; + $$; + select test_declare2_13(); + NOTICE: var1 - test_plpgsql.comp_type + NOTICE: var2 - test_plpgsql.comp_type[] + test_declare2_13 + ------------------ + + (1 row) + + -- + -- Test syntax: word1.word2.word3 %TYPE / %ROWTYPE [] + -- + -- test: block_label.row_variable.scalar_field % TYPE + create or replace function test_declare3_1() + returns void + language plpgsql + as $$ + <> + declare + var1 comp_type; + begin + declare + var2 block.var1.scalar_field%TYPE; + var3 block.var1.scalar_field%TYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + end; + end; + $$; + select test_declare3_1(); + NOTICE: var1 - comp_type + NOTICE: var2 - text + NOTICE: var3 - text[] + test_declare3_1 + ----------------- + + (1 row) + + -- test: block_label.row_variable.scalar_field % ROWTYPE - ERROR + create or replace function test_declare3_2() + returns void + language plpgsql + as $$ + <> + declare + var1 comp_type; + begin + declare + var2 block.var1.scalar_field%ROWTYPE; + begin + end; + end; + $$; + ERROR: schema "block" does not exist + CONTEXT: compilation of PL/pgSQL function "test_declare3_2" near line 7 + -- test: block_label.row_variable.row_field % TYPE - ERROR + create or replace function test_declare3_3() + returns void + language plpgsql + as $$ + <> + declare + var1 comp_type; + begin + declare + var2 block.var1.comp_field%TYPE; + begin + end; + end; + $$; + ERROR: schema "block" does not exist + CONTEXT: compilation of PL/pgSQL function "test_declare3_3" near line 7 + -- test: block_label.row_variable.row_field % ROWTYPE + create or replace function test_declare3_4() + returns void + language plpgsql + as $$ + <> + declare + var1 comp_type; + begin + declare + var2 block.var1.comp_field%ROWTYPE; + var3 block.var1.comp_field%ROWTYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + end; + end; + $$; + select test_declare3_4(); + NOTICE: var1 - comp_type + NOTICE: var2 - field_type + NOTICE: var3 - field_type[] + test_declare3_4 + ----------------- + + (1 row) + + -- test: row_variable.row_field.scalar_field % TYPE + create or replace function test_declare3_5() + returns void + language plpgsql + as $$ + declare + var1 comp_type; + begin + declare + var2 var1.comp_field.scalar_field%TYPE; + var3 var1.comp_field.scalar_field%TYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + end; + end; + $$; + select test_declare3_5(); + NOTICE: var1 - comp_type + NOTICE: var2 - boolean + NOTICE: var3 - boolean[] + test_declare3_5 + ----------------- + + (1 row) + + -- test: row_variable.row_field.scalar_field % ROWTYPE - ERROR + create or replace function test_declare3_6() + returns void + language plpgsql + as $$ + declare + var1 comp_type; + begin + declare + var2 var1.comp_field.scalar_field%ROWTYPE; + begin + end; + end; + $$; + ERROR: schema "var1" does not exist + CONTEXT: compilation of PL/pgSQL function "test_declare3_6" near line 6 + -- test: row_variable.row_field.row_field % TYPE - ERROR + create or replace function test_declare3_7() + returns void + language plpgsql + as $$ + declare + var1 comp_type; + begin + declare + var2 var1.comp_field.comp_field%TYPE; + begin + end; + end; + $$; + ERROR: schema "var1" does not exist + CONTEXT: compilation of PL/pgSQL function "test_declare3_7" near line 6 + -- test: row_variable.row_field.row_field % ROWTYPE + create or replace function test_declare3_8() + returns void + language plpgsql + as $$ + declare + var1 comp_type; + begin + declare + var2 var1.comp_field.comp_field%ROWTYPE; + var3 var1.comp_field.comp_field%ROWTYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + end; + end; + $$; + select test_declare3_8(); + NOTICE: var1 - comp_type + NOTICE: var2 - field_type2 + NOTICE: var3 - field_type2[] + test_declare3_8 + ----------------- + + (1 row) + + -- test: relation.row_field.scalar_field % TYPE + create or replace function test_declare3_9() + returns void + language plpgsql + as $$ + declare + var1 comp_type.comp_field.scalar_field%TYPE; + var2 comp_type.comp_field.scalar_field%TYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + end; + $$; + select test_declare3_9(); + NOTICE: var1 - boolean + NOTICE: var2 - boolean[] + test_declare3_9 + ----------------- + + (1 row) + + -- test: relation.row_field.scalar_field % ROWTYPE - ERROR + create or replace function test_declare3_10() + returns void + language plpgsql + as $$ + declare + var1 comp_type.comp_field.scalar_field%ROWTYPE; + begin + end; + $$; + ERROR: schema "comp_type" does not exist + CONTEXT: compilation of PL/pgSQL function "test_declare3_10" near line 3 + -- test: relation.row_field.row_field % TYPE - ERROR + create or replace function test_declare3_11() + returns void + language plpgsql + as $$ + declare + var1 comp_type.comp_field.comp_field%TYPE; + begin + end; + $$; + ERROR: schema "comp_type" does not exist + CONTEXT: compilation of PL/pgSQL function "test_declare3_11" near line 3 + -- test: relation.row_field.row_field % ROWTYPE + create or replace function test_declare3_12() + returns void + language plpgsql + as $$ + declare + var1 comp_type.comp_field.comp_field%ROWTYPE; + var2 comp_type.comp_field.comp_field%ROWTYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + end; + $$; + select test_declare3_12(); + NOTICE: var1 - field_type2 + NOTICE: var2 - field_type2[] + test_declare3_12 + ------------------ + + (1 row) + + -- test: schema.relation.scalar_field % TYPE + create or replace function test_declare3_13() + returns void + language plpgsql + as $$ + declare + var1 test_plpgsql.comp_type.scalar_field%TYPE; + var2 test_plpgsql.comp_type.scalar_field%TYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + end; + $$; + select test_declare3_13(); + NOTICE: var1 - integer + NOTICE: var2 - integer[] + test_declare3_13 + ------------------ + + (1 row) + + -- test: schema.relation.scalar_field % ROWTYPE - ERROR + create or replace function test_declare3_14() + returns void + language plpgsql + as $$ + declare + var1 test_plpgsql.comp_type.scalar_field%ROWTYPE; + begin + end; + $$; + ERROR: can't determine datatype + LINE 6: var1 test_plpgsql.comp_type.scalar_field%ROWTYPE; + ^ + -- test: schema.relation.row_field % TYPE - ERROR + create or replace function test_declare3_15() + returns void + language plpgsql + as $$ + declare + var1 test_plpgsql.comp_type.comp_field%TYPE; + begin + end; + $$; + ERROR: can't determine datatype + LINE 6: var1 test_plpgsql.comp_type.comp_field%TYPE; + ^ + -- test: schema.relation.row_field % ROWTYPE + create or replace function test_declare3_16() + returns void + language plpgsql + as $$ + declare + var1 test_plpgsql.comp_type.comp_field%ROWTYPE; + var2 test_plpgsql.comp_type.comp_field%ROWTYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + end; + $$; + select test_declare3_16(); + NOTICE: var1 - test_plpgsql.field_type + NOTICE: var2 - test_plpgsql.field_type[] + test_declare3_16 + ------------------ + + (1 row) + -- -- Test FOREACH over arrays -- *** a/src/test/regress/sql/plpgsql.sql --- b/src/test/regress/sql/plpgsql.sql *************** *** 3446,3451 **** --- 3446,4095 ---- drop function unreserved_test(); + -- + -- Test attributes %TYPE and %ROWTYPE + -- and also declaring arrays from copied types. + -- + + create type field_type2 as ( + scalar_field text + ); + + create type field_type as ( + scalar_field boolean, + comp_field field_type2 + ); + + create type comp_type as ( + scalar_field text, + comp_field field_type + ); + + create schema test_plpgsql; + + create type test_plpgsql.field_type as ( + field boolean + ); + + create type test_plpgsql.comp_type as ( + scalar_field integer, + comp_field test_plpgsql.field_type + ); + + create function test_type_and_rowtype_attribute(param1 integer, param2 comp_type) + returns void as $$ + declare + -- scalar types + var1 boolean; + var2 integer; + var3 varchar(5); + + -- record type + rec1 comp_type%ROWTYPE; + + -- copy types + copy_var1 var1%TYPE; + copy_var2 var2%TYPE; + copy_var3 var3%TYPE; + copy_rec1 rec1%ROWTYPE; + copy_param1 param1%TYPE; + copy_param2 param2%ROWTYPE; + copy_field comp_type.scalar_field%TYPE; + + -- declare arrays of copied types + -- many [] or [NUM] can be used, but all are folded during type construction + arr_var1 var1%TYPE[]; + arr_var2 var2%TYPE[1][2][3]; + arr_var3 var3%TYPE[][]; + arr_rec1 rec1%ROWTYPE[]; + arr_rec2 comp_type%ROWTYPE[]; + arr_param1 param1%TYPE[]; + arr_param2 param2%ROWTYPE[][][][][]; + arr_field comp_type.scalar_field%TYPE[][]; + begin + -- finally check types + raise notice '%', pg_typeof(copy_var1); + raise notice '%', pg_typeof(copy_var2); + raise notice '%', pg_typeof(copy_var3); + raise notice '%', pg_typeof(copy_rec1); + raise notice '%', pg_typeof(copy_param1); + raise notice '%', pg_typeof(copy_param2); + raise notice '%', pg_typeof(copy_field); + + raise notice '%', pg_typeof(arr_var1); + raise notice '%', pg_typeof(arr_var2); + raise notice '%', pg_typeof(arr_var3); + raise notice '%', pg_typeof(arr_rec1); + raise notice '%', pg_typeof(arr_param1); + raise notice '%', pg_typeof(arr_param2); + raise notice '%', pg_typeof(arr_field); + end + $$ language plpgsql; + + SELECT test_type_and_rowtype_attribute(NULL, NULL); + + + -- compilation should fail, because we don't allow + -- to declare array of arrays + create function test_type_attribute_array_of_array() + returns void as $$ + declare + arr integer[]; + arrarr arr%TYPE[]; -- <= error + begin + null; + end; + $$ language plpgsql; + + + -- compilation should fail, because we don't allow + -- to declare array of arrays + create or replace function test_rowtype_attribute_array_of_array() + returns void as $$ + declare + arr comp_type%ROWTYPE[]; + arr1 arr%TYPE; -- <= ok, type of 'arr' is comp_type[] + arrarr arr%ROWTYPE[]; -- <= error + begin + null; + end; + $$ language plpgsql; + + -- + -- Tests syntax: word %TYPE/%ROWTYPE [] + -- + + -- test variable%TYPE + create or replace function test_declare1_1() + returns void + language plpgsql + as $$ + declare + var1 integer; + var2 var1%TYPE; + var3 var2%TYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + end; + $$; + select test_declare1_1(); + + -- test scalar_variable%ROWTYPE - ERROR + create or replace function test_declare1_2() + returns void + language plpgsql + as $$ + declare + var1 integer; + var2 var1%ROWTYPE; + begin + null; + end; + $$; + + -- test record_variable%ROWTYPE - ERROR + create or replace function test_declare1_3() + returns void + language plpgsql + as $$ + declare + var1 record; + var2 var1%ROWTYPE; + begin + null; + end; + $$; + + -- test relation%ROWTYPE - OK + create or replace function test_declare1_4() + returns void + language plpgsql + as $$ + declare + var1 comp_type; + var2 comp_type%ROWTYPE; + var3 comp_type%ROWTYPE[]; + var4 var2%ROWTYPE; + var5 var2%ROWTYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + raise notice 'var4 - %', pg_typeof(var4); + raise notice 'var5 - %', pg_typeof(var5); + end; + $$; + select test_declare1_4(); + + -- + -- Tests syntax: word1.word2 %TYPE/%ROWTYPE [] + -- + + -- test: block_label.scalar_variable %TYPE + create or replace function test_declare2_1() + returns void + language plpgsql + as $$ + <> + declare + var1 integer; + begin + declare + var2 block.var1%TYPE; + var3 block.var1%TYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + end; + end; + $$; + select test_declare2_1(); + + -- test: block_label.scalar_variable %ROWTYPE - ERROR + create or replace function test_declare2_2() + returns void + language plpgsql + as $$ + <> + declare + var1 integer; + begin + declare + var2 block.var1%ROWTYPE; + begin + end; + end; + $$; + + -- test: block_label.row_variable %TYPE - ERROR + create or replace function test_declare2_3() + returns void + language plpgsql + as $$ + <> + declare + var1 comp_type; + begin + declare + var2 block.var1%TYPE; + begin + end; + end; + $$; + + -- test: block_label.row_variable %ROWTYPE + create or replace function test_declare2_4() + returns void + language plpgsql + as $$ + <> + declare + var1 comp_type; + begin + declare + var2 block.var1%ROWTYPE; + var3 block.var1%ROWTYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + end; + end; + $$; + select test_declare2_4(); + + -- test: row_variable.scalar_field % TYPE + create or replace function test_declare2_5() + returns void + language plpgsql + as $$ + declare + var1 comp_type; + var2 var1.scalar_field%TYPE; + var3 var1.scalar_field%TYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + end; + $$; + select test_declare2_5(); + + -- test: row_variable.scalar_field % ROWTYPE - ERROR + create or replace function test_declare2_6() + returns void + language plpgsql + as $$ + declare + var1 comp_type; + var2 var1.scalar_field%ROWTYPE; + begin + end; + $$; + + -- test: row_variable.row_field % TYPE - ERROR + create or replace function test_declare2_7() + returns void + language plpgsql + as $$ + declare + var1 comp_type; + var2 var1.comp_field%TYPE; + begin + end; + $$; + + -- test: row_variable.row_field % ROWTYPE + create or replace function test_declare2_8() + returns void + language plpgsql + as $$ + declare + var1 comp_type; + var2 var1.comp_field%ROWTYPE; + var3 var1.comp_field%ROWTYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + end; + $$; + select test_declare2_8(); + + -- test: relation.scalar_field % TYPE + create or replace function test_declare2_9() + returns void + language plpgsql + as $$ + declare + var1 comp_type.scalar_field%TYPE; + var2 comp_type.scalar_field%TYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + end; + $$; + select test_declare2_9(); + + -- test: relation.scalar_field % ROWTYPE - ERROR + create or replace function test_declare2_9() + returns void + language plpgsql + as $$ + declare + var1 comp_type.scalar_field%ROWTYPE; + begin + end; + $$; + + -- test: relation.row_field % TYPE - ERROR + create or replace function test_declare2_10() + returns void + language plpgsql + as $$ + declare + var1 comp_type.comp_field%TYPE; + begin + end; + $$; + + -- test: relation.row_field % ROWTYPE + create or replace function test_declare2_11() + returns void + language plpgsql + as $$ + declare + var1 comp_type.comp_field%ROWTYPE; + var2 comp_type.comp_field%ROWTYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + end; + $$; + select test_declare2_11(); + + -- test: schama.relation % TYPE - ERROR + create or replace function test_declare2_12() + returns void + language plpgsql + as $$ + declare + var1 test_plpgsql.comp_type%TYPE; + begin + end; + $$; + + -- test: schama.relation % ROWTYPE + create or replace function test_declare2_13() + returns void + language plpgsql + as $$ + declare + var1 test_plpgsql.comp_type%ROWTYPE; + var2 test_plpgsql.comp_type%ROWTYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + end; + $$; + select test_declare2_13(); + + -- + -- Test syntax: word1.word2.word3 %TYPE / %ROWTYPE [] + -- + + -- test: block_label.row_variable.scalar_field % TYPE + create or replace function test_declare3_1() + returns void + language plpgsql + as $$ + <> + declare + var1 comp_type; + begin + declare + var2 block.var1.scalar_field%TYPE; + var3 block.var1.scalar_field%TYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + end; + end; + $$; + select test_declare3_1(); + + -- test: block_label.row_variable.scalar_field % ROWTYPE - ERROR + create or replace function test_declare3_2() + returns void + language plpgsql + as $$ + <> + declare + var1 comp_type; + begin + declare + var2 block.var1.scalar_field%ROWTYPE; + begin + end; + end; + $$; + + -- test: block_label.row_variable.row_field % TYPE - ERROR + create or replace function test_declare3_3() + returns void + language plpgsql + as $$ + <> + declare + var1 comp_type; + begin + declare + var2 block.var1.comp_field%TYPE; + begin + end; + end; + $$; + + -- test: block_label.row_variable.row_field % ROWTYPE + create or replace function test_declare3_4() + returns void + language plpgsql + as $$ + <> + declare + var1 comp_type; + begin + declare + var2 block.var1.comp_field%ROWTYPE; + var3 block.var1.comp_field%ROWTYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + end; + end; + $$; + select test_declare3_4(); + + -- test: row_variable.row_field.scalar_field % TYPE + create or replace function test_declare3_5() + returns void + language plpgsql + as $$ + declare + var1 comp_type; + begin + declare + var2 var1.comp_field.scalar_field%TYPE; + var3 var1.comp_field.scalar_field%TYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + end; + end; + $$; + select test_declare3_5(); + + -- test: row_variable.row_field.scalar_field % ROWTYPE - ERROR + create or replace function test_declare3_6() + returns void + language plpgsql + as $$ + declare + var1 comp_type; + begin + declare + var2 var1.comp_field.scalar_field%ROWTYPE; + begin + end; + end; + $$; + + -- test: row_variable.row_field.row_field % TYPE - ERROR + create or replace function test_declare3_7() + returns void + language plpgsql + as $$ + declare + var1 comp_type; + begin + declare + var2 var1.comp_field.comp_field%TYPE; + begin + end; + end; + $$; + + -- test: row_variable.row_field.row_field % ROWTYPE + create or replace function test_declare3_8() + returns void + language plpgsql + as $$ + declare + var1 comp_type; + begin + declare + var2 var1.comp_field.comp_field%ROWTYPE; + var3 var1.comp_field.comp_field%ROWTYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + raise notice 'var3 - %', pg_typeof(var3); + end; + end; + $$; + select test_declare3_8(); + + -- test: relation.row_field.scalar_field % TYPE + create or replace function test_declare3_9() + returns void + language plpgsql + as $$ + declare + var1 comp_type.comp_field.scalar_field%TYPE; + var2 comp_type.comp_field.scalar_field%TYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + end; + $$; + select test_declare3_9(); + + -- test: relation.row_field.scalar_field % ROWTYPE - ERROR + create or replace function test_declare3_10() + returns void + language plpgsql + as $$ + declare + var1 comp_type.comp_field.scalar_field%ROWTYPE; + begin + end; + $$; + + -- test: relation.row_field.row_field % TYPE - ERROR + create or replace function test_declare3_11() + returns void + language plpgsql + as $$ + declare + var1 comp_type.comp_field.comp_field%TYPE; + begin + end; + $$; + + -- test: relation.row_field.row_field % ROWTYPE + create or replace function test_declare3_12() + returns void + language plpgsql + as $$ + declare + var1 comp_type.comp_field.comp_field%ROWTYPE; + var2 comp_type.comp_field.comp_field%ROWTYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + end; + $$; + select test_declare3_12(); + + -- test: schema.relation.scalar_field % TYPE + create or replace function test_declare3_13() + returns void + language plpgsql + as $$ + declare + var1 test_plpgsql.comp_type.scalar_field%TYPE; + var2 test_plpgsql.comp_type.scalar_field%TYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + end; + $$; + select test_declare3_13(); + + -- test: schema.relation.scalar_field % ROWTYPE - ERROR + create or replace function test_declare3_14() + returns void + language plpgsql + as $$ + declare + var1 test_plpgsql.comp_type.scalar_field%ROWTYPE; + begin + end; + $$; + + -- test: schema.relation.row_field % TYPE - ERROR + create or replace function test_declare3_15() + returns void + language plpgsql + as $$ + declare + var1 test_plpgsql.comp_type.comp_field%TYPE; + begin + end; + $$; + + -- test: schema.relation.row_field % ROWTYPE + create or replace function test_declare3_16() + returns void + language plpgsql + as $$ + declare + var1 test_plpgsql.comp_type.comp_field%ROWTYPE; + var2 test_plpgsql.comp_type.comp_field%ROWTYPE[]; + begin + raise notice 'var1 - %', pg_typeof(var1); + raise notice 'var2 - %', pg_typeof(var2); + end; + $$; + select test_declare3_16(); + + -- -- Test FOREACH over arrays --