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