 So, just before I get too far into this, I want to make sure you understand what this presentation is and what it isn't because I've already answered one question on PLPG SQL. I'm going to cover Postgres functions kind of in general and all the things that you can do with them and all the options in a fair amount of detail. But what I'm not going to do is go into a great deal of depth in writing PLPG SQL functions, for instance. There are some examples of that in here. The other thing I should mention is that this presentation derives from one that I've given several times before and I really need an hour and a half to do the full presentation. We only have 45 minutes, so there's no way to do that. So what I've done is I've moved a lot of the more detailed examples into an appendix in this slide deck and you can download the slides. I'm sure the conference will provide them somewhere. I've got a website, jocanway.com. By the way, my personal email address is just mail at jocanway.com. And I've got lists of presentations here and at the bottom of this list here. Not the fanciest website in the world, but it does for me. I've linked the PDF for this presentation. So there are a bunch of examples that we will definitely not get to today in there that go into more depth in some of the options. But what I'm going to try and do is cover the basic material, which is all of what options you have when you create functions. I guess I should go to a bigger. Okay, so first of all, options in Postgres are full fledged SQL. Functions in Postgres are full fledged SQL objects, but they're also used as the underlying mechanism that implements a lot of other objects in Postgres. And they're built in to the architecture from the ground up. That's pretty much why Postgres is known for its extensibility. And the reason Postgres is so extensible is because so many of the underlying features are implemented with functions and you can write your own functions in a variety of languages. In terms of where you can use functions, it's also pretty flexible. You can use them in the target list, so like this where you say select some function, feed it the column from a table. You can use them in the from clause. So this is a so-called set returning function. So the function itself represents a relation. It's rows and columns, and it can be joined with other tables or other functions or whatever you want to do with it. And they can also be used, of course, in the where clause as part of your predicate. These are just, this is not an exhaustive list, but this is a pretty good list of the things that are implemented underneath the covers by functions in Postgres. So when you use the plus operator to say one plus one equals two, that plus operator actually has a function that implements it. And so when you call one plus one, the arguments on either side of the operator get fed to that function as the two arguments, and then it returns a value. And that's how you implement the plus operator. Data types are all implemented with functions. So there's always an in function and out function for every data type. So that will translate between what the string representation or rendering of a value is with the binary and how it's stored. So a timestamp is stored as a binary value, but it's printed out in a whole variety of formats depending on how you have things configured and so on. Index methods, every data type that can be indexed by VTRI indexes has to have a comparator function that returns minus one zero or plus one depending on whether one argument is greater than equal to or less than the other argument. And so on, triggers, aggregates, in more recent releases ordered set aggregates and window functions, all done through functions. In terms of the types of functions that you can write, there's basic SQL functions. There's a whole variety of procedural languages that are slightly different than SQL functions. Then there's internal functions, which are the ones that are really built statically into the Postgres binary itself. And then you can furthermore write your own functions in C language if you want and load those. Another thing I'll mention as I go through this, in the slide deck, I've tried in a number of places to provide a link into the documentation where the subject that I'm on on that slide is discussed. So if you want to drill into more detail afterwards, you can find your way into the documentation. So in terms of SQL functions, their behavior, basically SQL functions are an arbitrary list of straight SQL delimited by semicolons. And unless you've declared the function to return void, the last statement in the SQL function has to return something. And that's what will be returned by the function. So you could have several statements that do things, inserts, updates, deletes, but that final statement needs to either be a select or it needs to be some kind of DML with a returning clause. Are people generally familiar with returning clauses on, as you say, a show of hands? How many people have seen insert or returning, that sort of thing? That's good. The arguments for SQL functions, in the more recent releases, they can be named arguments, but you can also use dollar sign one, dollar sign two to reference the arguments. They cannot be used as an identifier. So you can't pass an argument into a SQL function and then use that as the name of a table or the name of a column. You can actually pass in a composite type. So a composite type is, if I use the word composite or a tuple type, what I'm talking about is basically a row definition. So that would be column names with data types associated with it. So you can pass an entire composite type as an argument into a function and in that case you would use the dollar sign dot column name notation or the arg name dot column name notation. And on return, if you create, some of these things we're going to talk about later on in the slides, but you designate for a Postgres function whether it's returning basically a singleton, a single value, single row, or a set, meaning potentially, but not necessarily, multiple rows. And so if you've defined the SQL function to return a singleton and the last query in the function doesn't return anything, then you're going to get a null. So you'll actually get a row. It'll just have a null value in it. But if you've defined the function to return a set and the last thing, last statement returns no rows, then you're going to get an empty set. So there's kind of a subtle difference there. Procedural languages are written in something other than SQL and C. Basically, they're actually implemented through functions as well. So every procedural language has a function called the language handler that knows how to take the string that you've provided for the body of the function and pass it off to some kind of a compiler and executor that's going to turn that string into executable code. The library that implements the language handler is going to be loaded dynamically. So it's something that the first time you call a function in that language in a given session, that library has to get loaded. So there's some overhead associated with that. One thing to note, I don't actually have a slide that talks about it here, but there is a parameter in PostgreSQL.conf called preload shared libraries. And you can use that actually to cause one of these libraries to be loaded at the point when PostgreSQL first starts up. And the way Postgres works is that when you connect the back end that's listening for the connections will fork a back end for your connection. So if you've preloaded a library, at the time the fork happens, the library is already loaded and you've now eliminated some of that overhead. The procedural languages can be self-contained. So PLPG SQL is an example of one that's provided by the PostgreSQL project. It's maintained by the PostgreSQL project. It doesn't depend on anything external. But they can also be something that is externally linked. So PLPerl, PLpython, PLtickle, PLr, those are all examples of procedural languages that you've got this loadable language handler, but it in turn is also linked to some external dependency. So just a few words on PLPG SQL since it is kind of special. This is the one that is maintained by the PostgreSQL development group. It's self-contained. It's basically SQL plus procedural language elements that allow you to define variables, have conditionals, do loops, check for errors, have cursors, so on and so forth. So you have basically a full programming language there. And as was mentioned earlier before we actually got started, PLPG SQL is sort of loosely modeled after Oracle's PL SQL. So if you're familiar with PL SQL and Oracle, you'll find it looks pretty familiar. And if you're converting functions from Oracle, you'll find that if they're simple, they might just cut and paste. But if they're not simple, it won't be so simple. In all of the currently supported releases, PLPG SQL is automatically installed for you, so you don't have to install it. But you can uninstall it by using the drop extension syntax. And if you needed to reinstall it, you could use the create extension syntax. Now, I know I'm going pretty fast here, and I need to to get through the slides. But if someone has a question somewhere along the way, just stop me. So you said that ordinarily the first time that it's needed? That's right. So if you were to, say, install a newer version of one of these, would you have to tell Postgres somehow that you have a newer version? Or would you do a drop extension and a create extension? Or would you have to restart Postgres? I'm assuming you're talking about something that you've gotten externally. Because if it was one of the ones provided by Postgres, which I haven't actually covered yet, then the newer version would come with a newer version of Postgres, right? So if you're talking about something like PLR or PL Java or PL Ruby, then you would not need to do anything other than reconnect to Postgres. As long as that newer version was still compatible with the version of Postgres that you were running, it would simply get loaded the next time a session connected. Unless you're preloading shared libraries in which case you would have to restart Postgres. That's a very good point. This is something, as was mentioned, I develop PLR. So when I'm making changes to PLR and recompiling them, I basically just have to reconnect to Postgres and I see the effect of my change. Internal functions I mentioned are statically linked to the back end. So those are things that are provided with Postgres directly. And there's not a lot that you can do with these, but there is a syntax available so that you can essentially rename one of these things. So in the example here, there's an internal function called dsqrt for square root using double precision. And if you wanted to rename that as square root in this example here, you could do that by simply referencing that symbol and declaring this. So C language functions are obviously written in C. They're compiled into dynamically loadable objects, shared libraries, a link system, it would be a .so file. On Windows it would be a DLL. They're loaded, as we mentioned, on demand. There are a good number of examples of this in the contrib folder in the source. So if you have interest in writing a C function, there are plenty of examples. In order to really cover at any kind of depth, we would spend an entire day. So it's not something I can really cover here. Similar to PLPG SQL, to really give it due at any justice, you'd have to spend a half a day or a day covering it. So it's not something we can really cover in detail. I will point out that there's this PG module magic is a macro that you have to use, and this kind of applies to what you're just talking about. If the extension that you're trying to load was compiled against a different version of Postgres, a different major version of Postgres, that little bit is what ensures that you don't load it into a different version of Postgres, in order that you don't have unexpected crashes basically. Bad things can happen if you compile against one major version and then you try and load it in another, and so that prevents that from happening. In terms of what languages are available, PLPG SQL, PLPerl, PLpython, PLtickle are all in the main source tree for Postgres. They're all maintained by Postgres Development Group, and they're all distributed with Postgres, so those are kind of in all of your standard installations. There are a number of others available. There's a PLjava, actually I think at one point there were two different versions of PLjava. PLr, there's a PLv8, which v8 is Google's JavaScript engine, so that ties in nicely with some of the recent JSON capabilities that Postgres has, and there's even a PLshell written by Peter Eisenkraut, who's somewhere here at the, somewhere here at the conference, yeah. But potentially very dangerous. They're reasonably stable, and it's only on major versions. I don't know to what extent people are aware of the versioning in Postgres. But Postgres like 9.4.1, which is the current most recent release, the point one is what Postgres community generally calls a dot release. The dot release is just bug fixes. It's no enhancements, no new features, and they're intended to be, they're considered to be safer to upgrade than to not upgrade when we come out with a dot release. So those are completely backward compatible. Anything that loads in 9.4.0 is gonna load in 9.4.1 and 9.4.2. The issue that you would have is if it's compiled against 9.3, and then you try to use it in 9.4. But to get back to your question, you know, as I said, maintaining PLr, I have to keep it compatible with Postgres as major versions come out. And I can tell you that in the last two or three major versions, I may have had to modify half a dozen lines of code, if that, in order to keep it compiling against newer versions. So they're pretty stable. There's no guarantee, and occasionally there are some significant changes to the APIs in the back end, but it also depends on how deep you're reaching in and how fancy the things are that you're doing. So before we get into actual function creation, I wanna talk about dollar quoting. Who here is familiar with dollar quoting? So when you're creating a string variables as part of your SQL that need to have single quotes around, right? Well, the whole statement that you're trying to compile is also a string. So it would have to have single quotes around it. So years ago, you dealt with that by escaping the quotes, so inside your main string, you'd have doubled up quotes, and then if you had something that was being called in there that was calling another function, you might have quadrupled up quotes, and then you might even, I've written functions myself years ago, where I had eight and 16 single quotes. So it gets pretty hard to read and pretty ugly, pretty fast. Dollar quoting is a wonderful thing. Basically, you have double dollar sign, and you have an optional tag in between it, and you just match the tag at the beginning and the end of the string that you're quoting. So now everything inside there can actually have a different tag in it, and you can nest these things. Plus, you can have, at the very deepest level, you can have just single quoted things that look like normal and are easier to read. So you can see in this example here, I've used the dollar, underscore dollar as my dollar quote, but now I can just, and I have another dollar quote here with no tag at all, but then my string, hello, is just kind of single quoted around it. Another little side thing I wanted to touch on is anonymous functions. So this was added, again, I'm not sure it's in, was it 9.3? I was gonna say, I don't think it's in 9.0 or 9.1. I think it's in 9.2, though. It's a fairly recent addition is an anonymous function. So these things basically let you write a one-time use function. Now they're limited in that you can't pass arguments to them and they can't return anything, but they can be useful to do like administrative work. So here's an example of one, or in this example, what I'm doing is I'm looping through the Postgres internal tables to find all of the users who belong to a certain group. So my group I'm looking for is admin. I'm finding all the members of the group admin, and then I'm taking that string, the roll name, and I'm concatenating it to an alter a roll statement to set a custom configuration variable for all those users that are in the admin group, and I'm looping through them all. So I can do this as a kind of a one-time, I wanna assign the same value to all of these users, or I wanna do the same grant to all of these users. You can write one of these things, execute it, and then here's just a query that I'll show you how to prove that what I just did worked. So there's this roll name Rockstar, he belongs to the group admins, and now he's got this custom setting for workmen with 512 megs. And then one more thing, before we go into actual creation of functions, I just wanna mention replacing existing functions. There's quite a few of the Postgres objects that can be created also support this notion of create or replace. The reason for that is that objects in Postgres end up with dependencies. So if you've got a function that is implementing a trigger, and then you've got a trigger on a table that uses the function, if I wanted to update that function, I needed to fix it, fix a bug, change something. Without create or replace, you would have to then cascade that drop of the function and then recreate the function. So that could get also pretty ugly because that chain of dependencies could be quite long. So as long as you're not essentially changing the API of the function, you can do create or replace. And all of the things that refer to it actually refer to it by an underlying object identifier instead of the name. And we allow you to just replace the function without affecting all the things that depend on it. Yeah. The type hasn't changed, you're saying. And is the argument an in argument or just rename it. It's probably something we could do better, but I can't say that there's a good rationale for it. Well, you can call functions with named arguments now. In fact, I'm gonna show you examples. You can call a function with a named argument and you can supply the value and then call the function with the arguments out of the original order as long as you've named them. They're technically not because the, again, I've got a slide on this later on, but functions in Postgres can be overloaded. And the way that they're identified is based on the name of the function and the signature of the data types of the input arguments. And that name is actually not part of that. So we could resolve the function as long as you don't change the return type or the signature that defines which function it is we're talking about. I should think that we would be able to deal with that, but without looking at the code, I can't tell you why we don't. Any other questions around that? Okay, so this is the syntax for creating a function. You know, there's quite a bit going on here and by the end of this presentation, hopefully if I don't run out of time, I wanna touch on basically every bit of this so you understand at least at some level what all of these things do. And I'll tell you, as long as I've been using Postgres, I've been using Postgres for 16 or 17 years. And when I first sat down to put together the slides for this based on this premise of going through all these things, I learned stuff. So Postgres changes so fast over the years, things have been added, things have been changed. It's impossible to keep up with it all. So there are some, hopefully all of you will take away some new learnings from this. The arg mode, this is what's defining whether the argument is going into the function, coming out of the function. You can actually declare an argument to be going both in and out, and you declare an argument to be very dick, which means it can be one or more arguments that it represents. The default is in, so if you're not specifying an arg mode, you're getting an in argument, which is kind of the standard thing that people would expect with a function is that arguments go into it. And it's, although it's not required, it is good style to kind of line up your arguments so that the in arguments are first and then the in out and then the out arguments. As I just mentioned a minute ago, the function name plus the data types of all of the input arguments are what identify a function to Postgres. So when you call a function, those elements are what gets used to look up in the catalog and figure out precisely which function it is you're calling. So in this example, I've got an in argument, an in out argument, and an out argument, and I don't know how many people are familiar with this syntax, but you can use this values clause just like you have with an insert statement. When you use it like that, it's equivalent to a sub-slide. So this values with these arguments basically just produces a row. And for that matter, you'll see other examples later on where you can say comma, parentheses, and another set. You can actually produce multiple rows this way. Definitely useful for test and it can be useful for unusual production environments as well. For this case, these two arguments are going to define what comes back out of the function. So the next part of the function arguments is the arg name, not all languages necessarily have to support this. If you go way back, none of them did. I forget when it was added, but it's certainly in all, I think it was added in 9084. I think all really, all currently supported versions of Postgres do support argument naming. I think what is different is that SQL functions only started accepting argument names about nine one. So but other PLPG SQL could take argument names in 90. Yes? Well, you have to explicitly declare the in-out arguments. I'll cover that in a few slides. I'm actually starting to run out of time, so I'm gonna have to speed up a little bit, but I think you'll see that covered in when I get to the returns. So in this case, it's the same exact example, except I've named the arguments and now you can see that the columns when I do the select actually give me those, those named columns. And if I was joining on this with something else, I could refer to those. The argument types, this is the actual data type that I'm passing. They can be base array, composite or domain types. They can reference the type of a column using this kind of syntax. And they can also be these pseudo types, any element, any array. One of the things I'll mention here is that if you do use a pseudo type, let's say any element, any array for the arguments, the way those work is that whatever type gets used at runtime in a position that uses a pseudo type like that, the other positions that also use pseudo types have to be the same data type or a compatible one. So in other words, if I have any element in one position, any array as the return type for the function, then at runtime when I call that function, if I've passed an integer in that first position, then the return is gonna be an array of an integer. You can assign default expressions. So if an argument is not provided, the default expression will get used. And so you would do that by you can say default or you can say equals a value here. And then if you call the function without supplying that argument, it just gets defaulted to that value. Now once you've used a default on a column, on an argument, all of the following arguments also have to have defaults. And that's to aid in this reconciliation of which function it is we're trying to call. So if there's one default, then the following ones all have to have defaults. We talked about function overloading. Here's an example of it. I've created the same function called foo. One of them takes text, one takes int. They can each do something different. And when I call that same function name, in one case with a string, in one case with a number, it's going to choose the function based on the function name and the argument signature. Now this same thing actually could be done with a single function today if you wanted to using the pseudotex that I was just talking about. Okay, in terms of return types, you can say returns a particular type, or you can say returns table and provide a column definition list. So if you're using the return type method, again, you can have base scalar types. You can have composite types, domain types. You can specify the type of a column using the same syntax as you did for arguments. You can have these polymorphic types. And you can also have these special pseudotypes. So these, as I mentioned, a function implements each one of these procedural languages. And if you write one of those, it needs to return this language handler. If you write a foreign data wrapper, it needs to return fdw handler. Triggers, if you write a function that's going to implement a trigger, then your function needs to return trigger. And you can also have a function that returns voids. So if you have a function that's just being used for its side effects, don't want to return anything, you can have a return void. If you do return a return type, it has to match any in-out or out arguments that you've provided. But it doesn't necessarily return a set. So you can omit the returns clause entirely if you have out arguments. But if you do that, there's no way to specify that it's going to return a set and therefore it won't. And so in this example here, what I've said is these are both out arguments. I'm not supplying a return clause. Now, this time you see the values clause that I'm using actually has two rows in it, but I only get one row back and that's because this thing can't return a set. So now I'm going to give you a bunch of different examples of different ways, different styles to return things. And the only explanation I have for why you're going to see four or five different ways basically to do the same thing. And the only explanation I have for that is the fact that different people wanted to do things in different ways over the years and so functionality kept getting added to Postgres that allowed you to do the same thing in different ways. So some of these things have existed all the way back to the beginning of set returning functions in 7.3 and some of them are only in recent releases. So in this example, I'm doing two out arguments. I'm saying return set of record and when I select from the function, I get my two rows. In this case, this is kind of the way we did it at the very beginning of the set returning functions is you can actually create a type which is just a composite type. It represents a column definition list. Column names, column types. And then you can actually say you're returning that type. So that's the exact same function again when I call it. I still get the same result but I've just defined it in a different way. Here's an example where I'm saying it returns table and I'm giving it this column definition list in my function definition. So this is a way to do again the exact same thing but without the overhead of having to create the extra type because now the type is basically being created on the fly as we need it. And this is one of the original ways that we would have to do this is if you didn't specify when you created the function, it returns set of record but we haven't provided any kind of information about what that couple looks like. So in that case, you have to actually specify it at runtime. And although there's other ways now that obviously this example you don't need to do this method and this is kind of inconvenient at times. However, some functions that you write you won't know in advance what kinds of columns they're gonna return. So an example of that is dv-link. If you ever use dv-link, you use that to connect to another database, pull back data from it. It can have any number of rows and columns of any different kinds of types. And so the only way to deal with that is to specify it at runtime. You can't actually return set of scaling. So in this case I've said I'm returning a set of in and I just, I get multiple rows but obviously it's just one column. You can alias when you're returning even if you don't provide actual data types just like you can alias the table name you can actually alias the column names. So if you wanted to know that this column was gonna be called F1, you could alias like that. I do this all the time myself. If you ever used generate series, generate series just puts out a list of numbers basically, right? Well, if you don't alias a function like this when you call it, the column name is whatever the function by default. And I'm too lazy to type generate series in my query. So what I typically do is I say as G parentheses I and then I can reference it as G dot I. And you can actually call these things in the target list as long as it's in a language that supports it. So SQL functions can be called in the target list like this even if they're set returning. But what you get is not individual elements, you get the entire couple. So this is the way a tuple looks like when it's been rendered as a single entity. And if you wanted to split it back up, you could do this. If you put parentheses around it and say dot star, then you'd actually get those columns split back up. Now, why would you wanna do this? Back when set returning functions were first designed we anticipated the fact that you might wanna have two styles. Basically, one style is called materialized. In that case, whatever the function is doing to produce all the rows happens all at once and then the whole set gets returned. The other style is pipeline. Pipeline means as soon as I produce a row, I send it out. Now, if you've got a very big result set, that's gonna take a lot of room and memory and it's gonna take a while before it starts sending rows out. Now, PLPG SQL does not support pipelining at this point that's still something on the list of things to do. But SQL functions can be pipelined and this is the only way you get it pipelined is to call it in the target list. Okay, so now I'm gonna go through the attributes. We've already talked about all the different languages. You basically just specify the language that you wanna use. The main thing to mention here is that some of these procedural languages come in both a trusted and untrusted version. And PLPG SQL is considered trusted. And the difference is that a trusted language should not be able to get out to the system and do something dangerous. So therefore, you should be able to allow an untrusted user to write functions in a trusted language. Whereas an untrusted language, something like Perl, if you're using the untrusted version of Perl, whoever's writing the function could do a system call and they could do literally anything that Perl can do at the command line in your system, at least in terms of running as the user Postgres. So if they wanted to delete your entire data directory, they could do it. Window functions are kinda like aggregates in that they allow you to work on a set of rows, but instead of returning one row per group, they basically return a row for every item in the group. But it gives you access to the data in the other rows in terms of doing calculations. So you can do things like running sums, you can do ranks, you can do other sophisticated types analytics. Not enough time to really go into it at any depth here. But I do have examples in the appendix. Volatility, this is an important concept. Certain functions will provide a different result every time you call them, even if it's within one SQL statement. That's a volatile function. Certain functions are stable, meaning within a given SQL statement, with the same set of arguments, it's always gonna produce the same result. But from one call to another, that same function may produce a different result. And an immutable function is one that always produces the same result for the same arguments. If you wanna create an expressional index using over a function, it's gotta be immutable, but you can't have your index spitting out a different value the next time you call it. Here's just a quick example of the three different types just to illustrate it. The lower function is actually immutable. Whenever I call that, it always produces the same ABC for capital ABC. The now function is actually stable. So you see I've got one time for all three of these rows here, but a different time for these three rows in the second time I call this function. This clock time stamp is actually volatile. So every single row in these two different calls are going to produce a different value. Called on null input or returns null on null input are also, I don't mention it here, but there's an older syntax called strict for that one. Basically this has to do with, one of my arguments is a null, how am I gonna handle it? By default, if my function gets called with a null, the function's gonna execute, and if your function doesn't know how to deal with null, because nulls are sort of special in SQL, then it may produce surprising results. So if you're not gonna define the function to be returns null on null input, your function better be prepared to deal with nulls. And if you're writing a C function, it's doubly important because if you don't handle a null and a C function, you're gonna get a crash, and any kind of crash is potentially a route for an exploit, right? The other thing you should know is that if you define this as returns null on null input, then if there's a null argument, the function doesn't even get called, so you get no overhead from it. This is notion of leak proof. This has to do with security barrier views. The idea here is that if a function is defined as being sufficiently cheap, the planner could potentially push that function call down inside of a view, and it could occur before some filtering that's going on inside the view. And so if you allow a user who's trying to get at data they shouldn't be able to get at to write a function, it would get pushed into the view potentially if they make it look cheap enough to the planner, and it could do something like raise a notice in PLPG SQL that would show them information from rows that they weren't otherwise allowed to see. If you define a function, if you have a view that's defined as security barrier, then functions will not get pushed down by the planner unless they're marked as leak proof. And the reason that you might wanna do that is because of performance. If you can push the function down as low as possible, which is why Postgres might attempt to do that in the first place, you could improve performance. So if you know that a function has no risk of leaking information, you can define it as leak proof. But this is one where Postgres is gonna trust what you say, so you better be careful. Security invoker and security definer, this has to do with when the function is called at runtime, whose privileges is it going to run as? So if it's security invoker, then whoever's running the function, the function's gonna run as that user. If it's security definer, then whoever created the function, regardless of who uses it, as long as they've been granted access to execute that function, when they execute that function, it's sort of like a set UID function in a Linux or a Unix system where they suddenly get escalated privileges just for the length of the execution of that function. So you can allow them to do something they may not otherwise be able to do. And here's an example of that. Well, basically I've got this table. I don't wanna give the user direct access to the table, but I create this function that's gonna return some data from the table. I can set it as security definer, create that as Postgres, and then the user guest can't select from foo, but he can select from this function and you can see some data that I allow them to see. As I mentioned earlier, as far as the optimizer goes, functions are kind of a black box. The optimizer can't know how to parse every potential language function there is, and so it can't know how expensive it is nor how many rows it's expected to return. So the cost and the number of rows are defaulted to 100 units of cost and 1,000 rows. Sometimes that can be grossly off. And if you know that it's grossly off and it's affecting your query plan, you can actually set these values for the function, but you can only set them to one set of values. So this has some limited use. I've actually never run into a real life use case where I've done this, I'm sure there are people who have. And so this query just shows you how you could see what those optimizer hints were by looking in the system catalog for a particular function. I can set a local config. So any config that's able to be changed at session time, I can bind that to a function and have that value apply whenever that function gets called. So in this case, I want this particular function to always execute with a work mem of 512 meds. And this query will show you how to prove to yourself that looking in the system catalog that I've set work mem to 512 for this particular function. The function body, we've talked a lot about the string that makes up the function body, but it actually can also represent other things. So normally it's a string literal, it's parsed by whatever the language, if you're using a procedural language handler, it'll be parsed by that language. It can be just a single internal function name, as you saw an example earlier in the slide deck. In that case, it'll just be mapped to that symbol in C, in the C library. And it actually can be a path to a loadable library as well, as long as the function name itself matches exactly the C symbol that you're trying to call. And the body, as we talked about earlier, can be dollar quoted or it can be used single quotes with escaping. It can also be this other form, object file and link symbol. And this is more common when you're loading something that's been written in C. The object file is a pointer to the loadable object, and then the symbol is the actual name of the function in C that you're calling. And you can find, usually you wanna, if you're gonna do this, you usually want to, I assume I have a few minutes for questions at the end, right? Actually, we have about one minute officially left and our lunch starts in 10 minutes. So, in about a minute after Joe wraps up officially, feel free to leave and get in line or whatever. But if you'd like to stay. I've got just like two or three more slides. So I'm gonna try and hammer through them. If you wanna leave, I won't be offended. Just one thing to know here, when you do reference this object file, you typically, I've got an example on the next slide. You typically wanna use this libder, which is sort of a built-in symbol that knows where Postgres expects to find. It's objects. If you don't do that, you could have a hard coded absolute path, but then it makes what you've done non-portable, right? If you take, now dump this and try and load it on another server that's been configured for a different location, it won't work. But if you use libder, it will. And you can find out where that location is using this utility that comes with Postgres called pgconfig, dash-package libder. And that will tell you the location that's been bound to this libder symbol. So these are two examples of function bodies. You've seen lots of this first type. This one is for the, actually, for one of the functions in PLR, you reference it as libder slash plr. You don't even give it the .so. And again, this is to make it portable. Postgres will figure out if you're on a Linux system, it's .so. If you're on a Mac, I think it's .do or something like that. Windows system, it's .dll. But if you don't specify it, Postgres will figure it out based on what platform you're running on. Okay, so just two more slides. I wanted to give a couple of slightly more advanced examples. So this is an example of creating your own custom operator. If you've come from some other databases, you can use the plus symbol to concatenate strings, right? So in this case, I've created a function called sum that takes text, two arguments of text. And all it's gonna do is concatenate them with a space. And then by using this create operator call, I'm saying that the plus symbol is gonna use the procedure called sum with a left argument of text and a right argument of text. So now I've just extended Postgres so that I can say select hello plus world and it will concatenate those strings for me. Now this is an example of a custom aggregate. This concat ws comma is a function that's gonna take a text in any element. And it's gonna use concat with separator, which is a built-in function in Postgres. It's using a separator of a comma and it's taking two arguments. So now when I say create aggregate string ag that accepts any element, I'm gonna use that function and my state type, which is the type that's gonna get passed on from row to row within the group is text. And that's because this thing returns text. And so now when I say select string ag f1 from foo or I could group it if I wanted to, it's just gonna give me that column concatenated with commas as a string. So I am out of time. Like I said, there's 30 more slides full of more detailed examples. If you go to www.joeconway.com, you can get the slide deck immediately. If you wanna look through those and ask me any questions I'm here until Saturday, feel free to find me in the hallway. Send me an email, mail at joeconway.com or joe.conwayatcreditive.com. Either one and I'll do my best to answer your questions.