Yet another request came to me today for granting SELECT permissions. This time, the request was to give the user the correct privileges to use a view. Basically, you can use the same format that is used for tables as is used for views: After running a query like this Read more…
Someone had an issue today where they were using a string as if it were a regular expression. Not totally understanding what the desired end result would be, I had suggested they use something equivalent to Java’s Pattern.quote() function. It turned out that they really just wanted to determine if Read more…
Another DBA task that I seem to have to carry out is making sure that a specified user or group has all (CREATE & USAGE) permissions to all of the schemas (except pg_catalog for which they only get USAGE) in the database. For this reason, I end up using the Read more…
One of the things that I often had a difficult time with was finding a way to select the newest records from a table that had one or more columns to group by and then one column to order by. Let’s use the following table as an example: Version Name Read more…
Recently at work we had the need to convert a string parameter (passed into an iReport) into a table. For many people, the first thing that may come to mind is either the STRING_TO_ARRAY(...) function combined with the UNNEST(...) function. The biggest issue with using just those two functions is Read more…
Now to grant all access for roles on all of the functions in the database, you can use a query such as the following: After running this query, you will have all of the GRANT statements that are needed to give the user john_doe all access to the functions in Read more…
Once again, since the DBA is on vacation today, I have to assume some of his duties. It seems that nobody has access to anything in the development databases. After granting them permissions to all of the tables in the public schema (figured this out last time), I was asked Read more…
Something that I did before, but never documented until now was querying the database for all of the PL/pgSQL function definitions. In PostgreSQL, you can run the following to pull all of them along with the owner, schema, function name, and function definition: Of course, you can modify the above Read more…
One of the things that I love about string manipulation is the existence of regular expressions. For this reason, I have decided to share a few examples that may help those who are learning about regular expressions so that can understand them a bit better. JavaScript – General Variable Name Read more…
One of the things that I am not all that thrilled about is the fact that at times it is hard to find examples of PostgreSQL queries or plpgsql functions. For this reason, I want to help out anyone who would like to see an example of converting a query Read more…