{"id":1023,"date":"2018-09-25T10:08:36","date_gmt":"2018-09-25T08:08:36","guid":{"rendered":"http:\/\/eiseler.de\/wordpress\/?p=1023"},"modified":"2018-09-25T10:09:49","modified_gmt":"2018-09-25T08:09:49","slug":"postgresql-tipps-and-tricks","status":"publish","type":"post","link":"https:\/\/eiseler.de\/wordpress\/?p=1023","title":{"rendered":"Postgresql &#8211; Tipps and Tricks"},"content":{"rendered":"<p>Using PLSQL for setting Grants:<\/p>\n<p>&nbsp;<\/p>\n<pre>CREATE OR REPLACE FUNCTION set_all_sq_grants() RETURNS SETOF TEXT AS\r\n$BODY$\r\nDECLARE\r\nr record;\r\nBEGIN\r\nFOR r IN\r\nSELECT sequence_name, sequence_schema FROM information_schema.sequences WHERE sequence_schema = 'xxx'\r\nLOOP\r\n-- can do some processing here\r\nEXECUTE ' Grant all on ' || r.sequence_schema || '.' || r.sequence_name || ' to userXY' ;\r\n--RETURN NEXT r; -- return current row of SELECT\r\nEND LOOP;\r\nRETURN;\r\nEND\r\n$BODY$\r\nLANGUAGE plpgsql;<\/pre>\n<p>&#8212; und ausf\u00fchren<br \/>\nselect xxx.set_all_sq_grants();<\/p>\n<p>more about Postgres PLSQL: <a href=\"http:\/\/www.joeconway.com\/presentations\/SCALE13X-fun_with_functions.pdf\">Fun with Functions<\/a>.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<div class=\"sharedaddy sd-sharing-enabled\"><div class=\"robots-nocontent sd-block sd-social sd-social-icon-text sd-sharing\"><h3 class=\"sd-title\">Share this:<\/h3><div class=\"sd-content\"><ul><li class=\"share-facebook\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-facebook-1023\" class=\"share-facebook sd-button share-icon\" href=\"https:\/\/eiseler.de\/wordpress\/?p=1023&amp;share=facebook\" target=\"_blank\" title=\"Click to share on Facebook\"><span>Facebook<\/span><\/a><\/li><li class=\"share-twitter\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-twitter-1023\" class=\"share-twitter sd-button share-icon\" href=\"https:\/\/eiseler.de\/wordpress\/?p=1023&amp;share=twitter\" target=\"_blank\" title=\"Click to share on Twitter\"><span>Twitter<\/span><\/a><\/li><li class=\"share-linkedin\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-linkedin-1023\" class=\"share-linkedin sd-button share-icon\" href=\"https:\/\/eiseler.de\/wordpress\/?p=1023&amp;share=linkedin\" target=\"_blank\" title=\"Click to share on LinkedIn\"><span>LinkedIn<\/span><\/a><\/li><li class=\"share-pocket\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"\" class=\"share-pocket sd-button share-icon\" href=\"https:\/\/eiseler.de\/wordpress\/?p=1023&amp;share=pocket\" target=\"_blank\" title=\"Click to share on Pocket\"><span>Pocket<\/span><\/a><\/li><li class=\"share-end\"><\/li><\/ul><\/div><\/div><\/div>","protected":false},"excerpt":{"rendered":"<p>Using PLSQL for setting Grants: &nbsp; CREATE OR REPLACE FUNCTION set_all_sq_grants() RETURNS SETOF TEXT AS $BODY$ DECLARE r record; BEGIN FOR r IN SELECT sequence_name, sequence_schema FROM information_schema.sequences WHERE sequence_schema = &#8216;xxx&#8217; LOOP &#8212; can do some processing here EXECUTE &hellip; <a href=\"https:\/\/eiseler.de\/wordpress\/?p=1023\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n<div class=\"sharedaddy sd-sharing-enabled\"><div class=\"robots-nocontent sd-block sd-social sd-social-icon-text sd-sharing\"><h3 class=\"sd-title\">Share this:<\/h3><div class=\"sd-content\"><ul><li class=\"share-facebook\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-facebook-1023\" class=\"share-facebook sd-button share-icon\" href=\"https:\/\/eiseler.de\/wordpress\/?p=1023&amp;share=facebook\" target=\"_blank\" title=\"Click to share on Facebook\"><span>Facebook<\/span><\/a><\/li><li class=\"share-twitter\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-twitter-1023\" class=\"share-twitter sd-button share-icon\" href=\"https:\/\/eiseler.de\/wordpress\/?p=1023&amp;share=twitter\" target=\"_blank\" title=\"Click to share on Twitter\"><span>Twitter<\/span><\/a><\/li><li class=\"share-linkedin\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-linkedin-1023\" class=\"share-linkedin sd-button share-icon\" href=\"https:\/\/eiseler.de\/wordpress\/?p=1023&amp;share=linkedin\" target=\"_blank\" title=\"Click to share on LinkedIn\"><span>LinkedIn<\/span><\/a><\/li><li class=\"share-pocket\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"\" class=\"share-pocket sd-button share-icon\" href=\"https:\/\/eiseler.de\/wordpress\/?p=1023&amp;share=pocket\" target=\"_blank\" title=\"Click to share on Pocket\"><span>Pocket<\/span><\/a><\/li><li class=\"share-end\"><\/li><\/ul><\/div><\/div><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"spay_email":"","footnotes":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false},"categories":[1],"tags":[],"class_list":["post-1023","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"jetpack_featured_media_url":"","jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8zAuQ-gv","jetpack-related-posts":[{"id":1036,"url":"https:\/\/eiseler.de\/wordpress\/?p=1036","url_meta":{"origin":1023,"position":0},"title":"Connect Raspberry Pi & Arduino USB bidirectional","date":"25\/10\/2018","format":false,"excerpt":"How to connect an Arduino to a raspberry pi via USB bidirectional. The raspberry reads all from the serial and prints it. Next it reads a textile from a webpage and sends the text (one word) to serial, next a random car to the Arduino. Arduino got a LCD attached\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":417,"url":"https:\/\/eiseler.de\/wordpress\/?p=417","url_meta":{"origin":1023,"position":1},"title":"Arduino controlling Lego 4x4 (Powerfunctions IR)","date":"29\/10\/2013","format":false,"excerpt":"For the evenings at late summer holidays on a small island in the German north sea I bought a Lego 4x4 Offroader to build with the kids. The truck has 4x4 driving and steering, and is controlled by an ir remote. Coming home with the built truck\u00a0 I was curious\u2026","rel":"","context":"In &quot;Arduino&quot;","img":{"alt_text":"","src":"https:\/\/i2.wp.com\/eiseler.de\/wordpress\/wp-content\/uploads\/2013\/10\/IMG_5563-225x300.jpg?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":410,"url":"https:\/\/eiseler.de\/wordpress\/?p=410","url_meta":{"origin":1023,"position":2},"title":"PowerPoint 2007 trying to loop a movie. Bug.","date":"18\/10\/2013","format":false,"excerpt":"Strange behavior in PowerPoint. Tried to loop movies. Found out that this doesn't work with mp4 files, though it is shown in the dialog. After converting to avi it worked. Also when in mp4 format 2 movies on one slide play parallel. After converting to avi they played one after\u2026","rel":"","context":"In &quot;Tipps &amp; Tricks&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1279,"url":"https:\/\/eiseler.de\/wordpress\/?p=1279","url_meta":{"origin":1023,"position":3},"title":"Finding duplicates with SQL","date":"27\/01\/2020","format":false,"excerpt":"SELECT\u00a0name,\u00a0email,\u00a0COUNT(*)\u00a0FROM\u00a0users\u00a0GROUP\u00a0BY\u00a0name,\u00a0email\u00a0HAVING\u00a0COUNT(*)\u00a0>\u00a01","rel":"","context":"In &quot;Tipps &amp; Tricks&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1038,"url":"https:\/\/eiseler.de\/wordpress\/?p=1038","url_meta":{"origin":1023,"position":4},"title":"Raspberry pi & waveshare 2.7 e-ink Display & python","date":"11\/11\/2018","format":false,"excerpt":"Start with a headless raspberry, no need to connect monitor, keyboard:\u00a0https:\/\/raspberrypi.stackexchange.com\/questions\/10251\/prepare-sd-card-for-wifi-on-headless-pi Find the raspberry in your router and connect with ssh. Useullay steps are: sudo raspi-config sudo reboot (neu einloggen) sudo apt-get update -y sudo apt-get upgrade -y Here's some example python code for drawing text horizontal on the waveshare\u2026","rel":"","context":"In &quot;Hardware&quot;","img":{"alt_text":"","src":"https:\/\/i2.wp.com\/eiseler.de\/wordpress\/wp-content\/uploads\/2018\/11\/img_8466-e1541953811554.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":437,"url":"https:\/\/eiseler.de\/wordpress\/?p=437","url_meta":{"origin":1023,"position":5},"title":"Grove Tick Tock Shield Kit","date":"04\/11\/2013","format":false,"excerpt":"Giving the Tick Tock Shield Kit by Grove a try, I made same changes to the real time clock code. First I wanted to automaticaly change the brightness not only at the start of the code. And second I wanted the date to displayed to. Now it displays alternately with\u2026","rel":"","context":"In &quot;Arduino&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/eiseler.de\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1023","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/eiseler.de\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/eiseler.de\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/eiseler.de\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/eiseler.de\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1023"}],"version-history":[{"count":2,"href":"https:\/\/eiseler.de\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1023\/revisions"}],"predecessor-version":[{"id":1025,"href":"https:\/\/eiseler.de\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1023\/revisions\/1025"}],"wp:attachment":[{"href":"https:\/\/eiseler.de\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1023"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/eiseler.de\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1023"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/eiseler.de\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1023"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}