src/backend/catalog/pg_proc.c

   1 /*-------------------------------------------------------------------------
   2  *
   3  * pg_proc.c
   4  *        routines to support manipulation of the pg_proc relation
   5  *
   6  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
   7  * Portions Copyright (c) 1994, Regents of the University of California
   8  *
   9  *
  10  * IDENTIFICATION
  11  *        $Header: /cvsroot/pgsql/src/backend/catalog/pg_proc.c,v 1.86 2002/08/05 00:21:27 tgl Exp $
  12  *
  13  *-------------------------------------------------------------------------
  14  */
  15 #include "postgres.h"
  16
  17 #include "access/heapam.h"
  18 #include "catalog/catname.h"
  19 #include "catalog/dependency.h"
  20 #include "catalog/indexing.h"
  21 #include "catalog/pg_language.h"
  22 #include "catalog/pg_proc.h"
  23 #include "executor/executor.h"
  24 #include "fmgr.h"
  25 #include "miscadmin.h"
  26 #include "parser/parse_coerce.h"
  27 #include "parser/parse_expr.h"
  28 #include "parser/parse_relation.h"
  29 #include "parser/parse_type.h"
  30 #include "tcop/tcopprot.h"
  31 #include "utils/builtins.h"
  32 #include "utils/lsyscache.h"
  33 #include "utils/sets.h"
  34 #include "utils/syscache.h"
  35
  36
  37 static void checkretval(Oid rettype, char fn_typtype, List *queryTreeList);
  38 Datum fmgr_internal_validator(PG_FUNCTION_ARGS);
  39 Datum fmgr_c_validator(PG_FUNCTION_ARGS);
  40 Datum fmgr_sql_validator(PG_FUNCTION_ARGS);
  41
  42
  43 /* ----------------------------------------------------------------
  44  *              ProcedureCreate
  45  * ----------------------------------------------------------------
  46  */
  47 Oid
  48 ProcedureCreate(const char *procedureName,
  49                                 Oid procNamespace,
  50                                 bool replace,
  51                                 bool returnsSet,
  52                                 Oid returnType,
  53                                 Oid languageObjectId,
  54                                 Oid languageValidator,
  55                                 const char *prosrc,
  56                                 const char *probin,
  57                                 bool isAgg,
  58                                 bool security_definer,
  59                                 bool isStrict,
  60                                 char volatility,
  61                                 int parameterCount,
  62                                 const Oid *parameterTypes)
  63 {
  64         int                     i;
  65         Relation        rel;
  66         HeapTuple       tup;
  67         HeapTuple       oldtup;
  68         char            nulls[Natts_pg_proc];
  69         Datum           values[Natts_pg_proc];
  70         char            replaces[Natts_pg_proc];
  71         Oid                     typev[FUNC_MAX_ARGS];
  72         Oid                     relid;
  73         NameData        procname;
  74         TupleDesc       tupDesc;
  75         Oid                     retval;
  76         bool            is_update;
  77         ObjectAddress   myself,
  78                                         referenced;
  79
  80         /*
  81          * sanity checks
  82          */
  83         Assert(PointerIsValid(prosrc));
  84         Assert(PointerIsValid(probin));
  85
  86         if (parameterCount < 0 || parameterCount > FUNC_MAX_ARGS)
  87                 elog(ERROR, "functions cannot have more than %d arguments",
  88                          FUNC_MAX_ARGS);
  89
  90         /* Make sure we have a zero-padded param type array */
  91         MemSet(typev, 0, FUNC_MAX_ARGS * sizeof(Oid));
  92         if (parameterCount > 0)
  93                 memcpy(typev, parameterTypes, parameterCount * sizeof(Oid));
  94
  95         if (languageObjectId == SQLlanguageId)
  96         {
  97                 /*
  98                  * If this call is defining a set, check if the set is already
  99                  * defined by looking to see whether this call's function text
 100                  * matches a function already in pg_proc.  If so just return the
 101                  * OID of the existing set.
 102                  */
 103                 if (strcmp(procedureName, GENERICSETNAME) == 0)
 104                 {
 105 #ifdef SETS_FIXED
 106
 107                         /*
 108                          * The code below doesn't work any more because the PROSRC
 109                          * system cache and the pg_proc_prosrc_index have been
 110                          * removed. Instead a sequential heap scan or something better
 111                          * must get implemented. The reason for removing is that
 112                          * nbtree index crashes if sources exceed 2K --- what's likely
 113                          * for procedural languages.
 114                          *
 115                          * 1999/09/30 Jan
 116                          */
 117                         text       *prosrctext;
 118
 119                         prosrctext = DatumGetTextP(DirectFunctionCall1(textin,
 120                                                                                            CStringGetDatum(prosrc)));
 121                         retval = GetSysCacheOid(PROSRC,
 122                                                                         PointerGetDatum(prosrctext),
 123                                                                         0, 0, 0);
 124                         pfree(prosrctext);
 125                         if (OidIsValid(retval))
 126                                 return retval;
 127 #else
 128                         elog(ERROR, "lookup for procedure by source needs fix (Jan)");
 129 #endif   /* SETS_FIXED */
 130                 }
 131         }
 132
 133         /*
 134          * don't allow functions of complex types that have the same name as
 135          * existing attributes of the type
 136          */
 137         if (parameterCount == 1 && OidIsValid(typev[0]) &&
 138                 (relid = typeidTypeRelid(typev[0])) != 0 &&
 139                 get_attnum(relid, (char *) procedureName) != InvalidAttrNumber)
 140                 elog(ERROR, "method %s already an attribute of type %s",
 141                          procedureName, format_type_be(typev[0]));
 142
 143         /*
 144          * All seems OK; prepare the data to be inserted into pg_proc.
 145          */
 146
 147         for (i = 0; i < Natts_pg_proc; ++i)
 148         {
 149                 nulls[i] = ' ';
 150                 values[i] = (Datum) NULL;
 151                 replaces[i] = 'r';
 152         }
 153
 154         i = 0;
 155         namestrcpy(&procname, procedureName);
 156         values[i++] = NameGetDatum(&procname);          /* proname */
 157         values[i++] = ObjectIdGetDatum(procNamespace); /* pronamespace */
 158         values[i++] = Int32GetDatum(GetUserId());       /* proowner */
 159         values[i++] = ObjectIdGetDatum(languageObjectId); /* prolang */
 160         values[i++] = BoolGetDatum(isAgg);                      /* proisagg */
 161         values[i++] = BoolGetDatum(security_definer); /* prosecdef */
 162         values[i++] = BoolGetDatum(isStrict);           /* proisstrict */
 163         values[i++] = BoolGetDatum(returnsSet);         /* proretset */
 164         values[i++] = CharGetDatum(volatility);         /* provolatile */
 165         values[i++] = UInt16GetDatum(parameterCount); /* pronargs */
 166         values[i++] = ObjectIdGetDatum(returnType);     /* prorettype */
 167         values[i++] = PointerGetDatum(typev);           /* proargtypes */
 168         values[i++] = DirectFunctionCall1(textin,       /* prosrc */
 169                                                                           CStringGetDatum(prosrc));
 170         values[i++] = DirectFunctionCall1(textin,       /* probin */
 171                                                                           CStringGetDatum(probin));
 172         /* proacl will be handled below */
 173
 174         rel = heap_openr(ProcedureRelationName, RowExclusiveLock);
 175         tupDesc = rel->rd_att;
 176
 177         /* Check for pre-existing definition */
 178         oldtup = SearchSysCache(PROCNAMENSP,
 179                                                         PointerGetDatum(procedureName),
 180                                                         UInt16GetDatum(parameterCount),
 181                                                         PointerGetDatum(typev),
 182                                                         ObjectIdGetDatum(procNamespace));
 183
 184         if (HeapTupleIsValid(oldtup))
 185         {
 186                 /* There is one; okay to replace it? */
 187                 Form_pg_proc oldproc = (Form_pg_proc) GETSTRUCT(oldtup);
 188
 189                 if (!replace)
 190                         elog(ERROR, "function %s already exists with same argument types",
 191                                  procedureName);
 192                 if (GetUserId() != oldproc->proowner && !superuser())
 193                         elog(ERROR, "ProcedureCreate: you do not have permission to replace function %s",
 194                                  procedureName);
 195
 196                 /*
 197                  * Not okay to change the return type of the existing proc, since
 198                  * existing rules, views, etc may depend on the return type.
 199                  */
 200                 if (returnType != oldproc->prorettype ||
 201                         returnsSet != oldproc->proretset)
 202                         elog(ERROR, "ProcedureCreate: cannot change return type of existing function."
 203                                  "\n\tUse DROP FUNCTION first.");
 204
 205                 /* Can't change aggregate status, either */
 206                 if (oldproc->proisagg != isAgg)
 207                 {
 208                         if (oldproc->proisagg)
 209                                 elog(ERROR, "function %s is an aggregate",
 210                                          procedureName);
 211                         else
 212                                 elog(ERROR, "function %s is not an aggregate",
 213                                          procedureName);
 214                 }
 215
 216                 /* do not change existing ownership or permissions, either */
 217                 replaces[Anum_pg_proc_proowner-1] = ' ';
 218                 replaces[Anum_pg_proc_proacl-1] = ' ';
 219
 220                 /* Okay, do it... */
 221                 tup = heap_modifytuple(oldtup, rel, values, nulls, replaces);
 222                 simple_heap_update(rel, &tup->t_self, tup);
 223
 224                 ReleaseSysCache(oldtup);
 225                 is_update = true;
 226         }
 227         else
 228         {
 229                 /* Creating a new procedure */
 230
 231                 /* start out with empty permissions */
 232                 nulls[Anum_pg_proc_proacl-1] = 'n';
 233
 234                 AssertTupleDescHasOid(tupDesc);
 235                 tup = heap_formtuple(tupDesc, values, nulls);
 236                 simple_heap_insert(rel, tup);
 237                 is_update = false;
 238         }
 239
 240         /* Need to update indices for either the insert or update case */
 241         if (RelationGetForm(rel)->relhasindex)
 242         {
 243                 Relation        idescs[Num_pg_proc_indices];
 244
 245                 CatalogOpenIndices(Num_pg_proc_indices, Name_pg_proc_indices, idescs);
 246                 CatalogIndexInsert(idescs, Num_pg_proc_indices, rel, tup);
 247                 CatalogCloseIndices(Num_pg_proc_indices, idescs);
 248         }
 249
 250         AssertTupleDescHasOid(tupDesc);
 251         retval = HeapTupleGetOid(tup);
 252
 253         /*
 254          * Create dependencies for the new function.  If we are updating an
 255          * existing function, first delete any existing pg_depend entries.
 256          */
 257         if (is_update)
 258                 deleteDependencyRecordsFor(RelOid_pg_proc, retval);
 259
 260         myself.classId = RelOid_pg_proc;
 261         myself.objectId = retval;
 262         myself.objectSubId = 0;
 263
 264         /* dependency on namespace */
 265         referenced.classId = get_system_catalog_relid(NamespaceRelationName);
 266         referenced.objectId = procNamespace;
 267         referenced.objectSubId = 0;
 268         recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
 269
 270         /* dependency on implementation language */
 271         referenced.classId = get_system_catalog_relid(LanguageRelationName);
 272         referenced.objectId = languageObjectId;
 273         referenced.objectSubId = 0;
 274         recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
 275
 276         /* dependency on return type */
 277         if (OidIsValid(returnType))
 278         {
 279                 referenced.classId = RelOid_pg_type;
 280                 referenced.objectId = returnType;
 281                 referenced.objectSubId = 0;
 282                 recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
 283         }
 284
 285         /* dependency on input types */
 286         for (i = 0; i < parameterCount; i++)
 287         {
 288                 if (OidIsValid(typev[i]))
 289                 {
 290                         referenced.classId = RelOid_pg_type;
 291                         referenced.objectId = typev[i];
 292                         referenced.objectSubId = 0;
 293                         recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
 294                 }
 295         }
 296
 297         heap_freetuple(tup);
 298
 299         heap_close(rel, RowExclusiveLock);
 300
 301         /* Verify function body */
 302         if (OidIsValid(languageValidator))
 303         {
 304                 /* Advance command counter so new tuple can be seen by validator */
 305                 CommandCounterIncrement();
 306                 OidFunctionCall1(languageValidator, ObjectIdGetDatum(retval));
 307         }
 308
 309         return retval;
 310 }
 311
 312 /*
 313  * checkretval() -- check return value of a list of sql parse trees.
 314  *
 315  * The return value of a sql function is the value returned by
 316  * the final query in the function.  We do some ad-hoc define-time
 317  * type checking here to be sure that the user is returning the
 318  * type he claims.
 319  */
 320 static void
 321 checkretval(Oid rettype, char fn_typtype, List *queryTreeList)
 322 {
 323         Query      *parse;
 324         int                     cmd;
 325         List       *tlist;
 326         List       *tlistitem;
 327         int                     tlistlen;
 328         Oid                     typerelid;
 329         Oid                     restype;
 330         Relation        reln;
 331         int                     relnatts;               /* physical number of columns in rel */
 332         int                     rellogcols;             /* # of nondeleted columns in rel */
 333         int                     colindex;               /* physical column index */
 334
 335         /* guard against empty function body; OK only if no return type */
 336         if (queryTreeList == NIL)
 337         {
 338                 if (rettype != InvalidOid)
 339                         elog(ERROR, "function declared to return %s, but no SELECT provided",
 340                                  format_type_be(rettype));
 341                 return;
 342         }
 343
 344         /* find the final query */
 345         parse = (Query *) nth(length(queryTreeList) - 1, queryTreeList);
 346
 347         cmd = parse->commandType;
 348         tlist = parse->targetList;
 349
 350         /*
 351          * The last query must be a SELECT if and only if there is a return
 352          * type.
 353          */
 354         if (rettype == InvalidOid)
 355         {
 356                 if (cmd == CMD_SELECT)
 357                         elog(ERROR, "function declared with no return type, but final statement is a SELECT");
 358                 return;
 359         }
 360
 361         /* by here, the function is declared to return some type */
 362         if (cmd != CMD_SELECT)
 363                 elog(ERROR, "function declared to return %s, but final statement is not a SELECT",
 364                          format_type_be(rettype));
 365
 366         /*
 367          * Count the non-junk entries in the result targetlist.
 368          */
 369         tlistlen = ExecCleanTargetListLength(tlist);
 370
 371         typerelid = typeidTypeRelid(rettype);
 372
 373         if (fn_typtype == 'b')
 374         {
 375                 /* Shouldn't have a typerelid */
 376                 Assert(typerelid == InvalidOid);
 377
 378                 /*
 379                  * For base-type returns, the target list should have exactly one
 380                  * entry, and its type should agree with what the user declared. (As
 381                  * of Postgres 7.2, we accept binary-compatible types too.)
 382                  */
 383                 if (tlistlen != 1)
 384                         elog(ERROR, "function declared to return %s returns multiple columns in final SELECT",
 385                                  format_type_be(rettype));
 386
 387                 restype = ((TargetEntry *) lfirst(tlist))->resdom->restype;
 388                 if (!IsBinaryCompatible(restype, rettype))
 389                         elog(ERROR, "return type mismatch in function: declared to return %s, returns %s",
 390                                  format_type_be(rettype), format_type_be(restype));
 391         }
 392         else if (fn_typtype == 'c')
 393         {
 394                 /* Must have a typerelid */
 395                 Assert(typerelid != InvalidOid);
 396
 397                 /*
 398                  * If the target list is of length 1, and the type of the varnode in
 399                  * the target list matches the declared return type, this is okay.
 400                  * This can happen, for example, where the body of the function is
 401                  * 'SELECT func2()', where func2 has the same return type as the
 402                  * function that's calling it.
 403                  */
 404                 if (tlistlen == 1)
 405                 {
 406                         restype = ((TargetEntry *) lfirst(tlist))->resdom->restype;
 407                         if (IsBinaryCompatible(restype, rettype))
 408                                 return;
 409                 }
 410
 411                 /*
 412                  * Otherwise verify that the targetlist matches the return tuple type.
 413                  * This part of the typechecking is a hack. We look up the relation
 414                  * that is the declared return type, and scan the non-deleted
 415                  * attributes to ensure that they match the datatypes of the
 416                  * non-resjunk columns.
 417                  */
 418                 reln = heap_open(typerelid, AccessShareLock);
 419                 relnatts = reln->rd_rel->relnatts;
 420                 rellogcols = 0;                         /* we'll count nondeleted cols as we go */
 421                 colindex = 0;
 422
 423                 foreach(tlistitem, tlist)
 424                 {
 425                         TargetEntry *tle = (TargetEntry *) lfirst(tlistitem);
 426                         Form_pg_attribute attr;
 427                         Oid                     tletype;
 428                         Oid                     atttype;
 429
 430                         if (tle->resdom->resjunk)
 431                                 continue;
 432
 433                         do {
 434                                 colindex++;
 435                                 if (colindex > relnatts)
 436                                         elog(ERROR, "function declared to return %s does not SELECT the right number of columns (%d)",
 437                                                  format_type_be(rettype), rellogcols);
 438                                 attr = reln->rd_att->attrs[colindex - 1];
 439                         } while (attr->attisdropped);
 440                         rellogcols++;
 441
 442                         tletype = exprType(tle->expr);
 443                         atttype = attr->atttypid;
 444                         if (!IsBinaryCompatible(tletype, atttype))
 445                                 elog(ERROR, "function declared to return %s returns %s instead of %s at column %d",
 446                                          format_type_be(rettype),
 447                                          format_type_be(tletype),
 448                                          format_type_be(atttype),
 449                                          rellogcols);
 450                 }
 451
 452                 for (;;)
 453                 {
 454                         colindex++;
 455                         if (colindex > relnatts)
 456                                 break;
 457                         if (!reln->rd_att->attrs[colindex - 1]->attisdropped)
 458                                 rellogcols++;
 459                 }
 460
 461                 if (tlistlen != rellogcols)
 462                         elog(ERROR, "function declared to return %s does not SELECT the right number of columns (%d)",
 463                                  format_type_be(rettype), rellogcols);
 464
 465                 heap_close(reln, AccessShareLock);
 466         }
 467         else if (fn_typtype == 'p' && rettype == RECORDOID)
 468         {
 469                 /* Shouldn't have a typerelid */
 470                 Assert(typerelid == InvalidOid);
 471
 472                 /*
 473                  * For RECORD return type, defer this check until we get the
 474                  * first tuple.
 475                  */
 476         }
 477         else
 478                 elog(ERROR, "Unknown kind of return type specified for function");
 479 }
 480
 481
 482
 483 /*
 484  * Validator for internal functions
 485  *
 486  * Check that the given internal function name (the "prosrc" value) is
 487  * a known builtin function.
 488  */
 489 Datum
 490 fmgr_internal_validator(PG_FUNCTION_ARGS)
 491 {
 492         Oid                     funcoid = PG_GETARG_OID(0);
 493         HeapTuple       tuple;
 494         Form_pg_proc proc;
 495         bool            isnull;
 496         Datum           tmp;
 497         char       *prosrc;
 498
 499         tuple = SearchSysCache(PROCOID, funcoid, 0, 0, 0);
 500         if (!HeapTupleIsValid(tuple))
 501                 elog(ERROR, "cache lookup of function %u failed", funcoid);
 502         proc = (Form_pg_proc) GETSTRUCT(tuple);
 503
 504         tmp = SysCacheGetAttr(PROCOID, tuple, Anum_pg_proc_prosrc, &isnull);
 505         if (isnull)
 506                 elog(ERROR, "null prosrc");
 507         prosrc = DatumGetCString(DirectFunctionCall1(textout, tmp));
 508
 509         if (fmgr_internal_function(prosrc) == InvalidOid)
 510                 elog(ERROR, "there is no built-in function named \"%s\"", prosrc);
 511
 512         ReleaseSysCache(tuple);
 513         PG_RETURN_BOOL(true);
 514 }
 515
 516
 517
 518 /*
 519  * Validator for C language functions
 520  *
 521  * Make sure that the library file exists, is loadable, and contains
 522  * the specified link symbol. Also check for a valid function
 523  * information record.
 524  */
 525 Datum
 526 fmgr_c_validator(PG_FUNCTION_ARGS)
 527 {
 528         Oid                     funcoid = PG_GETARG_OID(0);
 529         void       *libraryhandle;
 530         HeapTuple       tuple;
 531         Form_pg_proc proc;
 532         bool            isnull;
 533         Datum           tmp;
 534         char       *prosrc;
 535         char       *probin;
 536
 537         tuple = SearchSysCache(PROCOID, funcoid, 0, 0, 0);
 538         if (!HeapTupleIsValid(tuple))
 539                 elog(ERROR, "cache lookup of function %u failed", funcoid);
 540         proc = (Form_pg_proc) GETSTRUCT(tuple);
 541
 542         tmp = SysCacheGetAttr(PROCOID, tuple, Anum_pg_proc_prosrc, &isnull);
 543         if (isnull)
 544                 elog(ERROR, "null prosrc");
 545         prosrc = DatumGetCString(DirectFunctionCall1(textout, tmp));
 546
 547         tmp = SysCacheGetAttr(PROCOID, tuple, Anum_pg_proc_probin, &isnull);
 548         if (isnull)
 549                 elog(ERROR, "null probin");
 550         probin = DatumGetCString(DirectFunctionCall1(textout, tmp));
 551
 552         (void) load_external_function(probin, prosrc, true, &libraryhandle);
 553         (void) fetch_finfo_record(libraryhandle, prosrc);
 554
 555         ReleaseSysCache(tuple);
 556         PG_RETURN_BOOL(true);
 557 }
 558
 559
 560
 561 /*
 562  * Validator for SQL language functions
 563  *
 564  * Parse it here in order to be sure that it contains no syntax
 565  * errors.
 566  */
 567 Datum
 568 fmgr_sql_validator(PG_FUNCTION_ARGS)
 569 {
 570         Oid                     funcoid = PG_GETARG_OID(0);
 571         HeapTuple       tuple;
 572         Form_pg_proc proc;
 573         List       *querytree_list;
 574         bool            isnull;
 575         Datum           tmp;
 576         char       *prosrc;
 577         char            functyptype;
 578
 579         tuple = SearchSysCache(PROCOID, funcoid, 0, 0, 0);
 580         if (!HeapTupleIsValid(tuple))
 581                 elog(ERROR, "cache lookup of function %u failed", funcoid);
 582
 583         proc = (Form_pg_proc) GETSTRUCT(tuple);
 584
 585         if (!OidIsValid(proc->prorettype))
 586                         elog(ERROR, "SQL functions cannot return type \"opaque\"");
 587
 588         tmp = SysCacheGetAttr(PROCOID, tuple, Anum_pg_proc_prosrc, &isnull);
 589         if (isnull)
 590                 elog(ERROR, "null prosrc");
 591
 592         prosrc = DatumGetCString(DirectFunctionCall1(textout, tmp));
 593
 594         /* check typtype to see if we have a predetermined return type */
 595         functyptype = typeid_get_typtype(proc->prorettype);
 596
 597         querytree_list = pg_parse_and_rewrite(prosrc, proc->proargtypes, proc->pronargs);
 598         checkretval(proc->prorettype, functyptype, querytree_list);
 599
 600         ReleaseSysCache(tuple);
 601         PG_RETURN_BOOL(true);
 602 }