本篇内容介绍了“PostgreSQL怎么实现用户自定义”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

Bison输入文件的组成:

%{Declarations%}Definitions%%Productions%%Usersubroutines一、User subroutines

用户自定义例程会原封不动的拷贝到gram.c文件中,详细请参考gram.c文件.

下面是gram.y用户自定义部分的源码

/**Thesignatureofthisfunctionisrequiredbybison.However,we*ignorethepassedyyllocandinsteadusethelasttokenposition*availablefromthescanner.*/staticvoidbase_yyerror(YYLTYPE*yylloc,core_yyscan_tyyscanner,constchar*msg){parser_yyerror(msg);}staticRawStmt*makeRawStmt(Node*stmt,intstmt_location){RawStmt*rs=makeNode(RawStmt);rs->stmt=stmt;rs->stmt_location=stmt_location;rs->stmt_len=0;/*mightgetchangedlater*/returnrs;}/*AdjustaRawStmttoreflectthatitdoesn'truntotheendofthestring*/staticvoidupdateRawStmtEnd(RawStmt*rs,intend_location){/**Ifwealreadysetthelength,don'tchangeit.Thisisforsituations*like"selectfoo;;selectbar"wherethesamestatementwillbelast*inthestringformorethanonesemicolon.*/if(rs->stmt_len>0)return;/*OK,updatelengthofRawStmt*/rs->stmt_len=end_location-rs->stmt_location;}staticNode*makeColumnRef(char*colname,List*indirection,intlocation,core_yyscan_tyyscanner){/**GenerateaColumnRefnode,withanA_Indirectionnodeaddedifthere*isanysubscriptinginthespecifiedindirectionlist.However,*anyfieldselectionatthestartoftheindirectionlistmustbe*transposedintothe"fields"partoftheColumnRefnode.*/ColumnRef*c=makeNode(ColumnRef);intnfields=0;ListCell*l;c->location=location;foreach(l,indirection){if(IsA(lfirst(l),A_Indices)){A_Indirection*i=makeNode(A_Indirection);if(nfields==0){/*easycase-allindirectiongoestoA_Indirection*/c->fields=list_make1(makeString(colname));i->indirection=check_indirection(indirection,yyscanner);}else{/*gottosplitthelistintwo*/i->indirection=check_indirection(list_copy_tail(indirection,nfields),yyscanner);indirection=list_truncate(indirection,nfields);c->fields=lcons(makeString(colname),indirection);}i->arg=(Node*)c;return(Node*)i;}elseif(IsA(lfirst(l),A_Star)){/*Weonlyallow'*'attheendofaColumnRef*/if(lnext(l)!=NULL)parser_yyerror("improperuseof\"*\"");}nfields++;}/*Nosubscripting,soallindirectiongetsaddedtofieldlist*/c->fields=lcons(makeString(colname),indirection);return(Node*)c;}staticNode*makeTypeCast(Node*arg,TypeName*typename,intlocation){TypeCast*n=makeNode(TypeCast);n->arg=arg;n->typeName=typename;n->location=location;return(Node*)n;}staticNode*makeStringConst(char*str,intlocation){A_Const*n=makeNode(A_Const);n->val.type=T_String;n->val.val.str=str;n->location=location;return(Node*)n;}staticNode*makeStringConstCast(char*str,intlocation,TypeName*typename){Node*s=makeStringConst(str,location);returnmakeTypeCast(s,typename,-1);}staticNode*makeIntConst(intval,intlocation){A_Const*n=makeNode(A_Const);n->val.type=T_Integer;n->val.val.ival=val;n->location=location;return(Node*)n;}staticNode*makeFloatConst(char*str,intlocation){A_Const*n=makeNode(A_Const);n->val.type=T_Float;n->val.val.str=str;n->location=location;return(Node*)n;}staticNode*makeBitStringConst(char*str,intlocation){A_Const*n=makeNode(A_Const);n->val.type=T_BitString;n->val.val.str=str;n->location=location;return(Node*)n;}staticNode*makeNullAConst(intlocation){A_Const*n=makeNode(A_Const);n->val.type=T_Null;n->location=location;return(Node*)n;}staticNode*makeAConst(Value*v,intlocation){Node*n;switch(v->type){caseT_Float:n=makeFloatConst(v->val.str,location);break;caseT_Integer:n=makeIntConst(v->val.ival,location);break;caseT_String:default:n=makeStringConst(v->val.str,location);break;}returnn;}/*makeBoolAConst()*CreateanA_Conststringnodeandputitinsideabooleancast.*/staticNode*makeBoolAConst(boolstate,intlocation){A_Const*n=makeNode(A_Const);n->val.type=T_String;n->val.val.str=(state?"t":"f");n->location=location;returnmakeTypeCast((Node*)n,SystemTypeName("bool"),-1);}/*makeRoleSpec*CreateaRoleSpecwiththegiventype*/staticRoleSpec*makeRoleSpec(RoleSpecTypetype,intlocation){RoleSpec*spec=makeNode(RoleSpec);spec->roletype=type;spec->location=location;returnspec;}/*check_qualified_name---checktheresultofqualified_nameproduction**It'seasiesttoletthegrammarproductionforqualified_nameallow*subscriptsand'*',whichwethenmustrejecthere.*/staticvoidcheck_qualified_name(List*names,core_yyscan_tyyscanner){ListCell*i;foreach(i,names){if(!IsA(lfirst(i),String))parser_yyerror("syntaxerror");}}/*check_func_name---checktheresultoffunc_nameproduction**It'seasiesttoletthegrammarproductionforfunc_nameallowsubscripts*and'*',whichwethenmustrejecthere.*/staticList*check_func_name(List*names,core_yyscan_tyyscanner){ListCell*i;foreach(i,names){if(!IsA(lfirst(i),String))parser_yyerror("syntaxerror");}returnnames;}/*check_indirection---checktheresultofindirectionproduction**Weonlyallow'*'attheendofthelist,butit'shardtoenforcethat*inthegrammar,sodoithere.*/staticList*check_indirection(List*indirection,core_yyscan_tyyscanner){ListCell*l;foreach(l,indirection){if(IsA(lfirst(l),A_Star)){if(lnext(l)!=NULL)parser_yyerror("improperuseof\"*\"");}}returnindirection;}/*extractArgTypes()*GivenalistofFunctionParameternodes,extractalistofjustthe*argumenttypes(TypeNames)forinputparametersonly.Thisiswhat*isneededtolookupanexistingfunction,whichiswhatiswantedby*theproductionsthatusethiscall.*/staticList*extractArgTypes(List*parameters){List*result=NIL;ListCell*i;foreach(i,parameters){FunctionParameter*p=(FunctionParameter*)lfirst(i);if(p->mode!=FUNC_PARAM_OUT&&p->mode!=FUNC_PARAM_TABLE)result=lappend(result,p->argType);}returnresult;}/*extractAggrArgTypes()*Asabove,butworkfromtheoutputoftheaggr_argsproduction.*/staticList*extractAggrArgTypes(List*aggrargs){Assert(list_length(aggrargs)==2);returnextractArgTypes((List*)linitial(aggrargs));}/*makeOrderedSetArgs()*Buildtheresultoftheaggr_argsproduction(whichseethecommentsfor).*Thishandlesonlythecasewherebothgivenlistsarenonempty,sothat*wehavetodealwithmultipleVARIADICarguments.*/staticList*makeOrderedSetArgs(List*directargs,List*orderedargs,core_yyscan_tyyscanner){FunctionParameter*lastd=(FunctionParameter*)llast(directargs);intndirectargs;/*NorestrictionunlesslastdirectargisVARIADIC*/if(lastd->mode==FUNC_PARAM_VARIADIC){FunctionParameter*firsto=(FunctionParameter*)linitial(orderedargs);/**Weignorethenames,thoughtheaggr_argproductionallowsthem;*itdoesn'tallowdefaultvalues,sothoseneednotbechecked.*/if(list_length(orderedargs)!=1||firsto->mode!=FUNC_PARAM_VARIADIC||!equal(lastd->argType,firsto->argType))ereport(ERROR,(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),errmsg("anordered-setaggregatewithaVARIADICdirectargumentmusthaveoneVARIADICaggregatedargumentofthesamedatatype"),parser_errposition(exprLocation((Node*)firsto))));/*OK,droptheduplicateVARIADICargumentfromtheinternalform*/orderedargs=NIL;}/*don'tmergeintothenextline,aslist_concatchangesdirectargs*/ndirectargs=list_length(directargs);returnlist_make2(list_concat(directargs,orderedargs),makeInteger(ndirectargs));}/*insertSelectOptions()*InsertORDERBY,etcintoanalready-constructedSelectStmt.**ThisroutineisjusttoavoidduplicatingcodeinSelectStmtproductions.*/staticvoidinsertSelectOptions(SelectStmt*stmt,List*sortClause,List*lockingClause,Node*limitOffset,Node*limitCount,WithClause*withClause,core_yyscan_tyyscanner){Assert(IsA(stmt,SelectStmt));/**Testsherearetorejectconstructslike*(SELECTfooORDERBYbar)ORDERBYbaz*/if(sortClause){if(stmt->sortClause)ereport(ERROR,(errcode(ERRCODE_SYNTAX_ERROR),errmsg("multipleORDERBYclausesnotallowed"),parser_errposition(exprLocation((Node*)sortClause))));stmt->sortClause=sortClause;}/*Wecanhandlemultiplelockingclauses,though*/stmt->lockingClause=list_concat(stmt->lockingClause,lockingClause);if(limitOffset){if(stmt->limitOffset)ereport(ERROR,(errcode(ERRCODE_SYNTAX_ERROR),errmsg("multipleOFFSETclausesnotallowed"),parser_errposition(exprLocation(limitOffset))));stmt->limitOffset=limitOffset;}if(limitCount){if(stmt->limitCount)ereport(ERROR,(errcode(ERRCODE_SYNTAX_ERROR),errmsg("multipleLIMITclausesnotallowed"),parser_errposition(exprLocation(limitCount))));stmt->limitCount=limitCount;}if(withClause){if(stmt->withClause)ereport(ERROR,(errcode(ERRCODE_SYNTAX_ERROR),errmsg("multipleWITHclausesnotallowed"),parser_errposition(exprLocation((Node*)withClause))));stmt->withClause=withClause;}}staticNode*makeSetOp(SetOperationop,boolall,Node*larg,Node*rarg){SelectStmt*n=makeNode(SelectStmt);n->op=op;n->all=all;n->larg=(SelectStmt*)larg;n->rarg=(SelectStmt*)rarg;return(Node*)n;}/*SystemFuncName()*Buildaproperly-qualifiedreferencetoabuilt-infunction.*/List*SystemFuncName(char*name){returnlist_make2(makeString("pg_catalog"),makeString(name));}/*SystemTypeName()*Buildaproperly-qualifiedreferencetoabuilt-intype.**typmodisdefaulted,butmaybechangedafterwardsbycaller.*Likewiseforthelocation.*/TypeName*SystemTypeName(char*name){returnmakeTypeNameFromNameList(list_make2(makeString("pg_catalog"),makeString(name)));}/*doNegate()*Handlenegationofanumericconstant.**Formerly,wedidthisherebecausetheoptimizercouldn'tcopewith*indexqualsthatlookedlike"var=-4"---itwants"var=const"*andaunaryminusoperatorappliedtoaconstantdidn'tqualify.*AsofPostgres7.0,thatproblemdoesn'texistanymorebecausethere*isaconstant-subexpressionsimplifierintheoptimizer.However,*there'sstillagoodreasonfordoingthishere,whichisthatwecan*postponecommittingtoaparticularinternalrepresentationforsimple*negativeconstants.It'sbettertoleave"-123.456"instringform*untilweknowwhatthedesiredtypeis.*/staticNode*doNegate(Node*n,intlocation){if(IsA(n,A_Const)){A_Const*con=(A_Const*)n;/*reporttheconstant'slocationasthatofthe'-'sign*/con->location=location;if(con->val.type==T_Integer){con->val.val.ival=-con->val.val.ival;returnn;}if(con->val.type==T_Float){doNegateFloat(&con->val);returnn;}}return(Node*)makeSimpleA_Expr(AEXPR_OP,"-",NULL,n,location);}staticvoiddoNegateFloat(Value*v){char*oldval=v->val.str;Assert(IsA(v,Float));if(*oldval=='+')oldval++;if(*oldval=='-')v->val.str=oldval+1;/*juststripthe'-'*/elsev->val.str=psprintf("-%s",oldval);}staticNode*makeAndExpr(Node*lexpr,Node*rexpr,intlocation){Node*lexp=lexpr;/*LookthroughAEXPR_PARENnodessotheydon'taffectflattening*/while(IsA(lexp,A_Expr)&&((A_Expr*)lexp)->kind==AEXPR_PAREN)lexp=((A_Expr*)lexp)->lexpr;/*Flatten"aANDbANDc..."toasingleBoolExpronsight*/if(IsA(lexp,BoolExpr)){BoolExpr*blexpr=(BoolExpr*)lexp;if(blexpr->boolop==AND_EXPR){blexpr->args=lappend(blexpr->args,rexpr);return(Node*)blexpr;}}return(Node*)makeBoolExpr(AND_EXPR,list_make2(lexpr,rexpr),location);}staticNode*makeOrExpr(Node*lexpr,Node*rexpr,intlocation){Node*lexp=lexpr;/*LookthroughAEXPR_PARENnodessotheydon'taffectflattening*/while(IsA(lexp,A_Expr)&&((A_Expr*)lexp)->kind==AEXPR_PAREN)lexp=((A_Expr*)lexp)->lexpr;/*Flatten"aORbORc..."toasingleBoolExpronsight*/if(IsA(lexp,BoolExpr)){BoolExpr*blexpr=(BoolExpr*)lexp;if(blexpr->boolop==OR_EXPR){blexpr->args=lappend(blexpr->args,rexpr);return(Node*)blexpr;}}return(Node*)makeBoolExpr(OR_EXPR,list_make2(lexpr,rexpr),location);}staticNode*makeNotExpr(Node*expr,intlocation){return(Node*)makeBoolExpr(NOT_EXPR,list_make1(expr),location);}staticNode*makeAArrayExpr(List*elements,intlocation){A_ArrayExpr*n=makeNode(A_ArrayExpr);n->elements=elements;n->location=location;return(Node*)n;}staticNode*makeSQLValueFunction(SQLValueFunctionOpop,int32typmod,intlocation){SQLValueFunction*svf=makeNode(SQLValueFunction);svf->op=op;/*svf->typewillbefilledduringparseanaXXXis*/svf->typmod=typmod;svf->location=location;return(Node*)svf;}staticNode*makeXmlExpr(XmlExprOpop,char*name,List*named_args,List*args,intlocation){XmlExpr*x=makeNode(XmlExpr);x->op=op;x->name=name;/**named_argsisalistofResTarget;it'llbesplitapartintoseparate*expressionandnamelistsintransformXmlExpr().*/x->named_args=named_args;x->arg_names=NIL;x->args=args;/*xmloption,ifrelevant,mustbefilledinbycaller*//*typeandtypmodwillbefilledinduringparseanaXXXis*/x->type=InvalidOid;/*marksthenodeasnotanalyzed*/x->location=location;return(Node*)x;}/**Mergetheinputandoutputparametersofatablefunction.*/staticList*mergeTableFuncParameters(List*func_args,List*columns){ListCell*lc;/*ExplicitOUTandINOUTparametersshouldn'tbeusedinthissyntax*/foreach(lc,func_args){FunctionParameter*p=(FunctionParameter*)lfirst(lc);if(p->mode!=FUNC_PARAM_IN&&p->mode!=FUNC_PARAM_VARIADIC)ereport(ERROR,(errcode(ERRCODE_SYNTAX_ERROR),errmsg("OUTandINOUTargumentsaren'tallowedinTABLEfunctions")));}returnlist_concat(func_args,columns);}/**DeterminereturntypeofaTABLEfunction.Asingleresultcolumn*returnssetofthatcolumn'stype;otherwisereturnsetofrecord.*/staticTypeName*TableFuncTypeName(List*columns){TypeName*result;if(list_length(columns)==1){FunctionParameter*p=(FunctionParameter*)linitial(columns);result=copyObject(p->argType);}elseresult=SystemTypeName("record");result->setof=true;returnresult;}/**Convertalistof(dotted)namestoaRangeVar(like*makeRangeVarFromNameList,butwithpositionsupport).The*"AnyName"referstotheany_nameproductioninthegrammar.*/staticRangeVar*makeRangeVarFromAnyName(List*names,intposition,core_yyscan_tyyscanner){RangeVar*r=makeNode(RangeVar);switch(list_length(names)){case1:r->catalogname=NULL;r->schemaname=NULL;r->relname=strVal(linitial(names));break;case2:r->catalogname=NULL;r->schemaname=strVal(linitial(names));r->relname=strVal(lsecond(names));break;case3:r->catalogname=strVal(linitial(names));r->schemaname=strVal(lsecond(names));r->relname=strVal(lthird(names));break;default:ereport(ERROR,(errcode(ERRCODE_SYNTAX_ERROR),errmsg("improperqualifiedname(toomanydottednames):%s",NameListToString(names)),parser_errposition(position)));break;}r->relpersistence=RELPERSISTENCE_PERMANENT;r->location=position;returnr;}/*SeparateConstraintnodesfromCOLLATEclausesinaColQualList*/staticvoidSplitColQualList(List*qualList,List**constraintList,CollateClause**collClause,core_yyscan_tyyscanner){ListCell*cell;ListCell*prev;ListCell*next;*collClause=NULL;prev=NULL;for(cell=list_head(qualList);cell;cell=next){Node*n=(Node*)lfirst(cell);next=lnext(cell);if(IsA(n,Constraint)){/*keepitinlist*/prev=cell;continue;}if(IsA(n,CollateClause)){CollateClause*c=(CollateClause*)n;if(*collClause)ereport(ERROR,(errcode(ERRCODE_SYNTAX_ERROR),errmsg("multipleCOLLATEclausesnotallowed"),parser_errposition(c->location)));*collClause=c;}elseelog(ERROR,"unexpectednodetype%d",(int)n->type);/*removenon-ConstraintnodesfromqualList*/qualList=list_delete_cell(qualList,cell,prev);}*constraintList=qualList;}/**ProcessresultofConstraintAttributeSpec,andsetappropriateboolflags*intheoutputcommandnode.PassNULLforanyflagstheparticular*commanddoesn'tsupport.*/staticvoidprocessCASbits(intcas_bits,intlocation,constchar*constrType,bool*deferrable,bool*initdeferred,bool*not_valid,bool*no_inherit,core_yyscan_tyyscanner){/*defaults*/if(deferrable)*deferrable=false;if(initdeferred)*initdeferred=false;if(not_valid)*not_valid=false;if(cas_bits&(CAS_DEFERRABLE|CAS_INITIALLY_DEFERRED)){if(deferrable)*deferrable=true;elseereport(ERROR,(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),/*translator:%sisCHECK,UNIQUE,orsimilar*/errmsg("%sconstraintscannotbemarkedDEFERRABLE",constrType),parser_errposition(location)));}if(cas_bits&CAS_INITIALLY_DEFERRED){if(initdeferred)*initdeferred=true;elseereport(ERROR,(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),/*translator:%sisCHECK,UNIQUE,orsimilar*/errmsg("%sconstraintscannotbemarkedDEFERRABLE",constrType),parser_errposition(location)));}if(cas_bits&CAS_NOT_VALID){if(not_valid)*not_valid=true;elseereport(ERROR,(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),/*translator:%sisCHECK,UNIQUE,orsimilar*/errmsg("%sconstraintscannotbemarkedNOTVALID",constrType),parser_errposition(location)));}if(cas_bits&CAS_NO_INHERIT){if(no_inherit)*no_inherit=true;elseereport(ERROR,(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),/*translator:%sisCHECK,UNIQUE,orsimilar*/errmsg("%sconstraintscannotbemarkedNOINHERIT",constrType),parser_errposition(location)));}}/*----------*Recursiveviewtransformation**Convert**CREATERECURSIVEVIEWrelname(aliases)ASquery**to**CREATEVIEWrelname(aliases)AS*WITHRECURSIVErelname(aliases)AS(query)*SELECTaliasesFROMrelname**Actually,justtheWITH...part,whichistheninsertedintotheoriginal*viewdefinitionasthequery.*----------*/staticNode*makeRecursiveViewSelect(char*relname,List*aliases,Node*query){SelectStmt*s=makeNode(SelectStmt);WithClause*w=makeNode(WithClause);CommonTableExpr*cte=makeNode(CommonTableExpr);List*tl=NIL;ListCell*lc;/*createcommontableexpression*/cte->ctename=relname;cte->aliascolnames=aliases;cte->ctequery=query;cte->location=-1;/*createWITHclauseandattachCTE*/w->recursive=true;w->ctes=list_make1(cte);w->location=-1;/*createtargetlistforthenewSELECTfromthealiaslistofthe*recursiveviewspecification*/foreach(lc,aliases){ResTarget*rt=makeNode(ResTarget);rt->name=NULL;rt->indirection=NIL;rt->val=makeColumnRef(strVal(lfirst(lc)),NIL,-1,0);rt->location=-1;tl=lappend(tl,rt);}/*createnewSELECTcombiningWITHclause,targetlist,andfakeFROM*clause*/s->withClause=w;s->targetList=tl;s->fromClause=list_make1(makeRangeVar(NULL,relname,-1));return(Node*)s;}/*parser_init()*Initializetoparseonequerystring*/voidparser_init(base_yy_extra_type*yyext){yyext->parsetree=NIL;/*incasegrammarforgetstosetit*/}

“PostgreSQL怎么实现用户自定义”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！