PostgresOpen 2019 Fascinating Reporting With Postgres psql

okay welcome to fascinating reporting with post Cara's psql and syn mail I am Christopher L Augustus people who know me just call me Chris and if you want download this presentation there should be a link somewhere or on the post grows up in 2019 z' website but if not you can go to the blue link right there at my place of business and download it from there so a little bit about me will be up most of my life in East Tennessee and one of the green counties in the Oak Ridge Knoxville a North City area except for three and a half years while I was over at Tennessee Technological University which is over a little bit in the militancy in the yellow county I currently am employed by a little company called ia we've actually just recently doubled our size and they contract me to the Department of Energy's Office of science and technical information and that's located Oak Ridge and what we do we collect the technical papers and even citations to journal articles from all the research the Department of Energy funds and we bring it in we process it and then we put it out on a website so that you as the taxpayers can get to this research that you paid for us at some level our data goes back to the 1940s and we even have journal articles cited back way beyond that on our personal level if you just want to hear me yep about stuff I have a twenty nine minute podcast I put out usually once a month and that links on there if you download this presentation all the links should be live so here's the plan for the class it's busted up into four pieces first we're going to look at the psql tool for that you get with postcards then we're gonna look at SQL then we're going to change gears for just a little bit we're gonna look at unix/linux in mail so if you create these reports it's nice to have away being able to send them out by email this is one way of doing it then the icing on the cake is an automated reporting system which is what you'll see is almost exactly what we're running at osteo today before I get too deep into kind of like my philosophy on programming there's the Paul McCartney philosophy and there's John Lennon of course to the Beatles Paul McCartney likes to start simple and complicated later if you have to so his song yesterday is a good example he woke up one morning and he was thinking scrambled eggs scrambled eggs and he wondered around the house sat down piano and he started doing the first few notes scrambled egg scrambled eggs pretty soon he had the first person yesterday all my troubles seemed so far away now it looks as over here stay oh I believe in yesterday gotten studio recorded the song twice the second take George Martin looked ahead their producer and he said hey you know we had some strings to it this would be perfect so he and Paul Kahn worked out what that would be accorded the strings and that's the song you can hear today John Lennon on the other hand he liked to start complicated and then you had to like mesh it down to something that was usable almost simple at that point strawberry fields forever is a good example of one of his songs he recorded take after take he had the Beatles do different things over and over and over and then he sat down with her producer George Martin said now let's put this together and they had to speed the song up slow the song down overdub to the point where some of the analog duplications they did got to the point at the end of the song gentleman says something and if you listen to the original recording it sounds like I buried Paul you listen to the earlier takes you can hear him clearly say cranberry sauce so I'm a Paul McCartney developer I'd like to start simple and if I got complicated later sure so for all the examples in this presentation I create a simple Doctor Who database and it's a schema and there's a link you can download that from if you want to run these yourself and basically it's a database of all the doctors stories from the classic series which would be one hundred and fifty nine and then as a detail to that I've got a 695 record episode table so everything I'm gonna query it's gonna be from that so now let's look at the tool psql according to the postgrads webpage it is a an air active terminal and it's a terminal based front end of both screws and there's few more things it does button Reds the important part that I keyed off of when we scoot Postgres is nine years ago it provides a number of meta commands and various shell like features to facilitate running scripts and on being a wide variety of tasks that's important for what's going to come up later so how do you connect to it and there's a bunch of ways and for the reporting that I do there this is the preferred way we do it you set up a dot PG pass file and Postgres has great documentation on it the one thing to be careful about is make sure you set the permissions to six zero zero so that only your Unix Linux user can see that and it actually says in documentation although I've not ever tried it out if you make the permissions be anything different it'll ignore the file and then of course if you make them differ in that someone else could easily get in there and see it then in my shell I set for environment variables which points to the database the host the port and then the user these are of course faked users this weren't actually work anywhere so if you just go into P SQL and you did a very simple select star from stories for just season one and ordered by the story ID and you get decent-looking output and that's where a lot of developers you stop okay yeah here it is here's your data when we show you is all the wonderful stuff you can do beyond that I put green arrow on order by and I impress this upon our junior developers a lot always order your results if you get results back and maybe Story twos first and then Story ones last it's like what's going on with that unless you're trying to alphabetize it if you're trying to alphabetize it by name and you should have ordered to name so I make that I've really try and plant that on our young developers always order your results and then there are the psql meta commands and they always start with backslash and Postgres 11 if you group some together have 56 major backslash commands and that's worth looking through the documentation on there's things you may never have to use so just a quick overview son of these the backslash Q is a clean way to exit from P SQL you can get help the backslash D command is useful for seeing details of objects you just backslash D and a table name and you'll see your table structure slash echo nice way just to put some output out and you'll see it later the backslash copy is an awesome way to get data into and out of Postgres that's worthy of a talk all unto itself and I'll show some great examples of it later brand-new in Postgres 11 and I've not had a chance to play with it are this backslash if commands we can do if bins and else's and then backslash set is useful for setting and viewing variables which we won't get into in this class but that is a great that great great feature and the backslash piece set which is useful for formatting which is exactly we're going to get into and if you just put in the pset command it gets it'll show you back in poster is 11 all these options we're only going to touch on the red bolded ones there's a whole lot more the online documentation for this is actually great and explains them well so going back to our default query we have the piece set line style ASCII which is the default if you do that with a border of 0 you get this table where you just have lines and they're excellent lines they're dashes under the column headings if I change the border to one now we get back to what looked like just the pure default when we win the psql if I Bank it to now we've got dashes pipe symbols and little pluses at the joins so you get this very nice-looking ASCII table and here's where things get interesting if your which probably everybody nowadays can support Unicode if you use the p-set bond style unicode you can get prettier letters or prettier characters previously we were showing ASCII there's something called old ASCII and if you're putting if your data has embedded line feeds or carriage returns there would be a difference between a scandal ASCII and like I said Unicode and here are the three examples of with Unicode with the different borders now you've got with a border 0 nice all looking lines across the top you go to border 1 now you've got horizontal and vertical lines and the nice Unicode get you called to cross in there and my son did point this out this presentation was made with LibreOffice running on a fedora 30 laptop so some things aren't just completely perfect but we're this is all open source so well sort of fun things but he pointed out there's a little bit of a disjoint in there and that's that is what it is but I paid nothing for the OS the presentation software and then my favorite output is the border style too with Unicode set and if someone asked me Chris I need you to get me the counts of something other real fast I'll go into psql dump my query in there put that out copy paste in my email send it they get and they go oh that looks nice so that's my default go to and I send people data then you can also set titles you can turn on/off the footers and you can turn on and off tuples only and the title is kind of fun if you set the title it'll put a towel at the top of your table of output if you put it in double quotes you can actually have multiple words and once you set a title it is set until you either call p-set title with nothing or you change it to another value and that can zap you sometimes you'd be around query and a window you have and then an hour later it's like okay I'm gonna run another query you just pop a query in there comes up it's like we're that tile come from oh yeah an hour ago I'd set that that's just how it works there's a backslash see as a shortcut for that you can also turn on and off the little X Rose footer at the bottom and then for tuples only you can make it show just the rows without the header or with the header in the rows so for a title it's pretty easy you can see I say doctor who season one and then after I run the query now I have this nice tile at the top if I say footer off that little Rose goes away on the previous slide you can see there were eight rows now it's gone and then tuples only for reporting probably isn't very useful usually most of the time but here there's no headers on the top just your columns of data that be barring some sort of data export if for some reason copy didn't do what you need it to do and then you can actually change the format everything we've looked at so far has been aligned but there's actually eight others so there's one edit in Postgres Don dot five I think I failed to mention this all these examples run in Postgres non dot four to eleven which are currently supported versions unless I make a little mention and here's one that wouldn't run in non dot four buts asciidoc I'm not exactly sure what that's useful for but it is possible to do here's a good example of AA wand and I'll the box you'll pipe between your fields and a return between your records and that and both of those are as it shows at the bottom you can set those hTML is great for email reports and it produces HTML code one thing to be aware of there's not an HTML start up document or an end or even the body starter end and you'd have to put that in yourself and there's a reason for that and you'll see that towards the end of the presentation and then if you ran that this is output you might see depending on what your browser is and then if you're into this format you can say format latex or latex depending on you're supposed to say that and apparently people stay up on that long arguing this it's you look online it's like there's heated arguments it's so common example of that one time there were several arguing how you pronounced the capital of Kentucky and they came to me oh how you pronounce the capital of Kentucky I say you pronounce it Louisville and said no no no it's Louisville another front no its Louisville and I said Chris can you answer it's like I can answer it real fast I pronounce it Frankfort you can use that one someone could use that tomorrow morning at the we have one of those then there's also the trough ms format which dates back to the 1960s and I had to go look that up is like some of these I just don't know anything about and I was like oh okay so Postgres supports a document format all the way back to the 60s then there's the expanded and it's kind of hard to explain that they're trying to hear the best way to understand expand is just to look at it if you run it with expanded off which is a default you see tables like you normally do the IDs and the names if you expand that then you see record one and here's your story ID and its value across from that and then you see the name and it's back across from that this is great where if you have a hundred and I think it's thirty seven wide Road tables like we have one at my place of business that's about the only way you're gonna see a record you can't get a term well wide enough to try to display one hundred and thirty seven columns this way I can actually display it and then this the slash backslash copy command is proof Postgres developers love us the this is one of the most powerful commands there's also an SQL command that's copy but you have to be a super user to use it backslash copy any user that can log into the database and query records from a table could use it and here's two little examples where I select just the season one stories from the stories table and I send it to standard out common delimited CSV header and that's what looks like the second example I'm doing the same thing but I'm sending it to a file DW CSV and now put that in whatever your current wherever PS coils running from on your Linux box they'll put they're not sure what exactly happens on Windows of course you want to be safe always give it a full path to where it goes and then you don't have to worry about well where did it go then you always know you can output and copy in three different forms CSV for comma delimited and there's a text format there's a binary format there's outside reporting there might be reasons you'd use text or binary so that's an introduction to formatting in P SQL now we're going to jump on to PostgreSQL this class is marked as a beginner class and there's some things in here they're gonna be novice some things are going to be beginner and then there's some things that go complex and if you lose your place in any of this that's okay because the next section it's almost like we're gonna change big ears and it's okay if you lose out something here just two blinks two poster as documentation so if you want do something as simple as changing the column headings to something a human can understand then right after each field in double quotes you can put like four instead of having s tid I'm saying counts on so okay that's like the story number and then instead of s T name I've got story name and four I'm a reporting point of view that's what non dad base users would like to see here's a little bit more complex idea you can embed sub-queries and select clauses and we can get pretty nasty percent of stuff we do here's something pretty simple same queries before for season one I just want to count the number of episodes each story has so in there in the red text where it says select count star that's just an aggregate function and I'll want count every record for unearthly child and there's four of them and I can't ever sing one of them it counts up four and then I list the tables the Epps's table with lowercase e the stories table with a lowercase s and that's where in that where clause I can join the episodes to the stories and then they're good and you can take my word for it unless you just know this those are the correct counts we can do something very similar with groups we're going to join the stories to episodes and this is an SQL standard way of doing it on and then you there's your story ideas coming from story and episodes I've moved the count out now I've moved the count down to the third parameter and then I have to group the stories and he any field I show from the stories table I've got to name and my group by clause then there's the always important my closet then I get the exact same answers more proof Postgres developers love us and this is not in the SQL standard is when you're joining there's the using if you happen to be joining on the exact same field name between tables and this could be one or many fields you by using the using Clause it will do that for you and so again you see the exact same results but it is important to know your data and that this is where you can get fouled up sometimes what if I had a story with no episodes does any one of the big doctor he fan in here got see one there was a story and 1979 that they started making big strike at the BBC and they yanked the plug on it and the videotape and film from it we're throwing the archive and they forgot about so I've got a story that there's a production record there's a production record for it Chadda but as you can see an episode can't there's zero so when I do this with sub-query I see four episodes for all the earlier stories of the season and when they ran out of time for shot ax 0 if I join I get shot of disappears it's not there anymore if I use an outer joint which basically says this is a left outer join I'm saying in the stories tab a stable show me a record for every story in regards to where you got records for the episodes it doesn't matter I want to see every record for the stories but now there's a big problem it acts like sha de has one episode and that's because of in the third line the counts star I'm counting every record returned so I'm actually miscounting shot us story as an episode there there's a way to fix that very simple fix instead of doing a count star count the epe number field and if that field is null I don't count it if there's a value in that field I count it so now shot of is back to having zero episodes like it's supposed to know your data that's best of Oz I can give them that and then a little slot on sorting if you sort by episode name you get kind of a weird thing for sorts before one alphabetically and that doesn't look good so if you sort by the EP number field then one comes for two two becomes four three and this looks like good data a race in all fairness nine years ago I wondered who on earth needs arrays and relational database said I don't I don't know why this was before discovering the rich set of functions and postgrads to handle arrays and while at my place of work we have very few arrays and tables and one of the places I really scratched my head what are you doing there but it's rare but in reporting using a razor dosti has become quite common and there's no reference to working what's up and this is where we come move moved into more of the intermediate areas of post grows the array AG function aggregates an array so in before I could show from again says season 17 parts one two three four on separate lines if I a great that together I put it into an array all in one line and that's postgrads is default way of outputting arrays I don't know if that can be changed but that's not really to human friendly and not wanting to pick on shotta too much it's got a null rate down at the bottom so they're pretty it up the developers added a red string so you pass it in an array for a first parameter and the second parameter I picked as a semicolon in space when out puts it now you can see clearly parts one semicolon part 2 semicolon part 3 semicolon part 4 and the nola rate for shotta just shows up as nothing because there were never any complete episodes for that one so now we're gonna move on to temporary tables and temporary tables don't have to be complex the they usually are used for complex things but the idea behind them is not so if I want to go to my story table and select the ID the story name then jump over to the episodes table and grab an array of all the episode names and I would like to put this in a temporary table well the syntax is actually pretty easy create temporary table and I've made the story episodes table name blue so you can see it appear in the next examples a creep that as and just rerun that same query so now I can just do select star from stories episodes and if I had a huge screen here you could see all of the stories they're showing that then a command the backslash D Story episodes I can actually see how that table is arranged and the green arrow is pointing to the episode names is a character varying array at this point now why it says PG underscore temperance core 3 is a schema that I don't know that's one of the mysteries of Postgres important thing about temporary tables they last only as long as your session when you end your session like the backslash Q it's gone that's actually sort of nice that you won't have your dad base littered with temporary tables like that so if you're running through these impuls through the scripts at home notice I got this little thing it says stay in the session because on the next slide I'm going to use the Postgres function unnecessary all the trouble I went through Rae aggregation to take the column values and put up into a big long array you can undo it with a nest and there you can see the story of an earthly child and there's its four names the second story of the daleks there's its seven edge instruction only had two and there it is now what happens if you try this would Chadha well there's a NOLA ray and just has a blank there and at this point in the session because we're going to create a new temporary table just to prove one point this time I made the episode names semicolon delimited which mimics our legacy data we have at Asti or will have authors will have author semicolon author semicolon author semicolon author semicolon author and we would like to do something with them well here's a very simple way you can do that so the first stock create the table I created it and I do a select to make sure it looks right and the Dow looks right I have a semicolon the middle list of my episode names I use the function surprise surprise string to Ray and I convert that semicolon delimited string back into an array and you can see in the two columns when I have episodes as I put them into the table and then I've converted them to an array and then we have distinct and distinct on distinct is part the SQL standard so it's very portable other places it's great for showing unique or distinct values back in a queries result set stinked on with some fields in parentheses is not part of the SQL standard and it can be a use as a shortcut of very basic windowing functions and in the description of this talk it's that we're not gonna go into quagmire of windowing functions that's has been and is a great topic for a whole session layer so four distinct I want to see the distinct episode names for season 17 I run the query and I see okay well there's part 4 part 1 part 3 and part 2 but then there's a blank layer and five rows well the blank is our friend in shadow you can configure in psql the null or if you don't have to worry about that because maybe this won't we won't be R and P SQL there's a coalesce and that's the most difficult function to spell correctly it's thank goodness that PG admin 3 + PG admin 4 will you had that turned on the key word will turn blue when you get it right it's like it's the worst thing to type in but once you get typed incorrectly now it's turned my empty shot of episodes into a none one nice thing about coalesce you can keep adding functions or keep adding parameters to the function so you might have three fields that you want to try show something and if all three of those fields come up null you can always have a string literal at the end and if everything evaluates to null and coalesce I think I'll just pass on through a KO less and then we've got distinct on and this is useful if you want to do something where you have a detailed table and you want to show the first string from it or you want to show or the first feel from it or last field so in this case I wanted to see what is the episode number of the first story or the first episode of a story and what was its name so this is what you do is in the distinct on in parentheses you can put the 1 or more fields you want to be the can think of the exact thing to call it but it's it's the base of what you're in this case it's this story named actually in this case it's the story ID and that's why it has to be unique it's like I was telling some of the guys at lunch I'm gonna get to a slot I'm gonna like I don't remember the slide well this time I'm going through the presentation this is a slide don't remember so we got that out of the way so then you show the story the episode number and then the first episode name so for an earthly child the first episode ought to be up someone and it is and the first episode name just happens also be the story name and it goes through all that and then what's important in Watson read you have to order by whatever you put in the parenthesis at the top s T underscore ID and then that next field you order beyond that says which will be the first ones since that's a normal ascending order it's going to be water record comes up first and now always be the first episode jumping over what is the last episode name of each story now this is a little bit harder to do all I did was on the order by I switched that over to the episode number sorting order to this descending and now for our fleet Riley to look at it you see episode number for the fire-maker yeah and all the way down the list that's the way it goes funny story just something the watch out for when you're developing one of our developers went to Stack Overflow and need to do something like this and found out oh I can use distinct on and used it and he checked and the version we had postcards and development supported that he did okay everything went along fine it went through testing which was that same version production was supposed to been upgraded and it got laid and his report got deployed to protection and first time I went to use it boom it blew up they come hollering to me yeah why's it not working mother that's it well give me the query let's see what's going on gives me the query I look and I go to my version because I didn't know the syntax went into the my version of the documentation like doesn't exist and then I thought oh I went to the current version of the documentation there was distinct on I'm looking at going well this is awesome function shame we all have in production yet she won't have it for a month so I went through redid it with windowing functions gave him back his query and said okay put this in there and then put ticket in when we get upgraded in production then you can revert back to your other code because that actually is great good stuff distinct on is one of the reasons why postcards developers love the users okay now we can change gears and I'm gonna get a sip of water we're gonna look at UNIX or Linux sin male and sin male is an electronic mail transport agent it sends messages to one or more recipients routing the message over water networks are necessary syn mail does internetworking forwarding as necessary to deliver the messages to the correct place and when I looked up how old this was it dates back to 1983 for all my examples the cinema has a ton of parameters I'm going to paul mccartney around again I'm going the simple route I'm gonna use CIN mail - T which basically means read the message you make for recipients one little word of note I tried to configure syn mail on two different Linux boxes at home I couldn't do it but I went through examples other people's stuff I never made it work at my place of business it works beautiful so that's a more of a system administration thing I'm more of a developer DBA so that doesn't make sense all these examples of mail actually had to take them to work to make them work so if you can't make some mill work it's okay a good ol hello world example and this is UNIX stuff okay I just make a temporary text file and make it easy for you to cut paste I cat it to temp text I send it to a fake user subject test one and then the actual message of the body is hello world this is test one control D and then you cat your temp file to send mail with that - T option and then there in the pale blue box that's what you'd get a little bit more complex if you want to send it to the email to two users you want CC to additional users and you want a blind carbon copy to additional users and you want to make it look like it came from another user you can do that and when you see that example in the blue box you can see the twos the cc's and no BCC if you want seen utf-8 text-based emails now this is very complicated just a little bit more you have to set the mom type - version 1.0 set the content type to text slash plain semicolon and here's an important part to get your utf-8 going into it character set equals u TF a utf-8 and then the content-disposition inline which means it gonna be what you see in the message of email I just wanted to note and that's a u TF nope or unicode utf-8 note emphasized the size because if I left it small you would just look like a blob on the screen and when you send that email there's your note for HTML it's not that much more complicated you Mohammed op stays the same the content type you make a text slash HTML and if you're using Postgres by default it's an a utf-8 database so use utf-8 the it's an inline message and you make a very simple test HTML document and when it's sent you get an HTML email out and if you're already putting two and two together you can see what's going to happen in the next section here's the most complex example I've got this is putting a binary file into an email and we're 12 11 ish minutes of time left so I'm not gonna get too deep into this the blue arrow points to multi-part mixed and then you set a boundary and everywhere that boundary shows up I put a yellow arrow the first time I looked up how to do this and it took a while to figure out how to do this there's not seem like there wasn't an awesome example in one place in the internet how to do that but the first example I looked up they used that character set the q1 w/e and I'm just stuck with it it works so after the first boundary then this looks familiar you say okay I want put a text HTML character set this position in line still and then I do a little simple HTML document into HTML document there's the important boundary again and then I say I want a text plain utf-8 a base64 encoding and content-disposition is no longer in line it's an attachment and then I just I can name it anything I want it's smart to keep at the name of the document you to be this case I'm going to try seeing a PDF for the moment save that because we're going to go to part two and then I have to cat the document and this is an actual PDF to you you in code which turns 8-bit characters and the Simba characters that can be sent by email and that's a technically a base64 type of way of doing that I put the document name in there again and I can catenate that to my temp file and then I have to put a boundary concatenates a temp file at the end but the proofs in the pudding I send it and there's my PDF I'm gonna double click on it opens it if you don't you you encode that PDF you get digital goo now we're to the final section mmm a little less than 10 minutes left this is the automated reporting system it's the icing on the cake we're there a complex automated reporting system is why a co-worker of mine wanted to do and he had all these complex basically is a John linen application he wanted to do I know why he wanted to do it he had to keep running these complex reports into our one application he said there's got to be an easier way and I was like I don't know the last point ultimately it never progressed beyond wishes and talk but this idea didn't die in my mind I kept thinking how about Paul McCartney I approached this I want a simple automated reporting system so I have I had a pain that users would have me write these reports and then they come back and they say oh I need you to change recipients ok I'd change them I'd have to pull out the source control change it put it back in test it all this stuff yeah it got crazy I thought if I could just have a list of these somewhere it'd be so much easier I didn't have a lot of time to do this so I just took a little baby steps so phase 1 I can't put simple requirements I need a base tables store recipients and I need a function to get those recipients out of the data tables and I need to assign a unique report ID to each one of those so I can easily someone says I need report number 23 fixed I need these people put on it or and these people taken off ok makes a ton easier than some name that doesn't mean much to me the test phase one I just needed to find a report that was low profile or barely used and so fairly easy requirements but names are changed to protect innocents but these are actually the two tables we are using at Asti ones reports and we got a report ID a report title and a report description the ID I don't even have a sequence to sign those why do is my create a new report go production I say okay the highest table number or house report number is this I mentally add one to it and I add the record in myself and then for the emails I really don't have to do anything but in development and test I just reuse that same number and then I go through and I say okay we're per ID in the fields and that's where you can be a to field a CC a BCC or the from and then of course got an email address and now through in a little complication and active flag someone's on vacation don't want to be bombarded with emails so they get back and there's hundreds and they're waiting on them you can always click them off then there's a dad based function now created and this is actually an SQL function and one of the cool parts is up at the green arrow it returns a set of text so this is like it's almost like calling a table when you call this thing you get text out and then I want to select from trying to get like I'm the whole point is function is to make sin male friendly guess you almost call it header or idea we have the twos the cc's the BC season the from I just format that in there so I can put that in and then I can catenate that to email dress and boom there it is ready to go the yellow arrow is always important ordered by the fields so if I'm debugging I can get in there very easily and go okay from here and I can always know the predicted order and the red arrow is just pointing out that this is an SQL function it is not a peel PG SQL function populating the tables is easy this is more here just to show you how to do it if you use the one go through these examples at home and then there's a shell script so I start off and in the shell script you can pass it one parameter and that's the doctor who season and so I grabbed that from what's coming in and store that as a human readable variable then as you saw earlier on the presentation I set the export the database name host port and user and the password sing in the dot PG pass file and then I sign it the report ID of one next part of the script I call psql but the - - quad option that way you don't get all the banners open source software banners and stuff at the front you know sending of that thing out on your email this little unix linux thing the end of SQL everything between that in the of SQL and what will be at the on the fourth slide is all gonna be sent to postgrads and then we've got auch in F gets rid of line feeds because they you can get LAN feeds in your data at the top so then we go through and just kind of going through this real fast I do some pset formatting which was from the first section and then I select from my function I wrote reports in their recipients I passed that that report ID and there are so Morris if Ian's then I go to the subject line and I just select that out of my reports table and why I really do it work a lot of times I'll concatenate on to it ran on this date at this time because our data changes and it's important to know that the dad changed that star an HTML email nothing we haven't seen before and we go through and eventually mill with the screen we run the query and then nice little thing I do at the end the report idea always stick at the end report so someone sends me a report and says add me to this I go in events like okay I'll add you then we sent it to user s Benson mail - t and the email goes out and here's some tests of phase one you know had to do this at work because I'm gonna get some mail to work from home and there are season 17 and shot it what it's missing story or missing episodes okay this is a complex example of sending email with a CSV attachment and we're just about out of time so I'm going just jump through this it works almost exactly like the syn mail one did but the whole point is you send it out and up there at the top is your common delimited file which someone can open up and read in Excel LibreOffice calc where we one do so for Phase two which we've actually accomplished a job web Bebek a java web application was written which allows our end users to go in and add or take themselves off the report so I'd only have to go tinker with the table anymore recipients can be pulled from other tables what we had case where we need to do that we had users that every user from a certain place is supposed to get report so we did that and we're using it more we use it more than just in scripts and PS kill reports we have some Java programs just direct we calling this stuff and using it also so it into even though a simple ended up being ELLs ply love things and we just assigned report ID 79 there aren't sending nine because were retired some but we're probably out seventy five reports are using that reporting simple reporting system then of course we've got ideas for future phases but the important part is there's still no plans to create that complex one so trying to end on time thank you for coming and important thing is if you get that link at the bottom you can download PDF version of this presentation run through these examples and I guess I can answer quick we got break coming up anyway but I can answer I'll answer any questions you got thank you oh okay yeah yeah does anyone have no questions let's that makes it easy on me but do feel free to ask me out there

Channel: Postgres Open

Views: 1,863

Rating: 4.8823528 out of 5

Keywords:

Id: GMiJs7YSzXM

Length: 50min 0sec (3000 seconds)

Published: Thu Sep 19 2019