Thread: A table of magic constants
Hello,
My reason/excuse for asking what I'll eventually ask is that I'm a new PostgreSQL practitioner so the amount of things I don't know about PostgreSQL is depressingly large.
While reading through the recent "Row level security - notes and questions" thread I saw this SQL statement:
CREATE POLICY accounts_policy ON testrls.accounts
FOR ALL
TO users
USING (true)
WITH CHECK (username = SESSION_USER);
While reading through the recent "Row level security - notes and questions" thread I saw this SQL statement:
CREATE POLICY accounts_policy ON testrls.accounts
FOR ALL
TO users
USING (true)
WITH CHECK (username = SESSION_USER);
The bit that is the catalyst for this e-mail is: SESSION_USER.
As a recent convert to the Church of Postgres I've been consuming vast amounts of information on PostgreSQL, and SESSION_USER is not the first nor only, what I'm calling magic constant, that I've seen. Off the top of my head, other examples that I've encountered are CURRENT_USER and CURRENT_TIMESTAMP.
So my question is this, is there a reference table in the documentation that I haven't found yet that lists all magic constants and their meaning? And if not in the official documentation is it in the wiki?
Thanks,
Dane
On 11/07/2015 20:07, Dane Foster wrote: <snip> > As a recent convert to the Church of Postgres I've been consuming vast Welcome to the One True Faith! :-) > amounts of information on PostgreSQL, and SESSION_USER is not the first > nor only, what I'm calling magic constant, that I've seen. Off the top > of my head, other examples that I've encountered are CURRENT_USER and > CURRENT_TIMESTAMP. > > So my question is this, is there a reference table in the documentation > that I haven't found yet that lists all magic constants and their > meaning? And if not in the official documentation is it in the wiki? session_user, current_timestamp and current_user are all functions, not magic constants: http://www.postgresql.org/docs/9.4/static/functions-datetime.html http://www.postgresql.org/docs/9.4/static/functions-info.html I hope this helps, Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 07/11/2015 12:21 PM, Raymond O'Donnell wrote: > On 11/07/2015 20:07, Dane Foster wrote: > > <snip> > >> As a recent convert to the Church of Postgres I've been consuming vast > > Welcome to the One True Faith! :-) > >> amounts of information on PostgreSQL, and SESSION_USER is not the first >> nor only, what I'm calling magic constant, that I've seen. Off the top >> of my head, other examples that I've encountered are CURRENT_USER and >> CURRENT_TIMESTAMP. >> >> So my question is this, is there a reference table in the documentation >> that I haven't found yet that lists all magic constants and their >> meaning? And if not in the official documentation is it in the wiki? > > session_user, current_timestamp and current_user are all functions, not > magic constants: > > http://www.postgresql.org/docs/9.4/static/functions-datetime.html > http://www.postgresql.org/docs/9.4/static/functions-info.html > > I hope this helps, To add to this. In the documentation section: http://www.postgresql.org/docs/9.4/interactive/index.html there is a Search box where you can enter the word/phrase you are looking for. If all else fails there is the Index: http://www.postgresql.org/docs/9.4/interactive/index.html > > Ray. > -- Adrian Klaver adrian.klaver@aklaver.com
Hello Adrian,
Thank you for the additional reference links but my concern was less about how to find out what a function (formerly magic constant) that I encountered in the wild did but more about having a list that would educate newcomers/me about what is automatically available for use. For example, in the RLS example from my original message, had I the same or similar need as the poster I would not have been able to formulate the policy that I quoted because I had no clue that SESSION_USER even existed. Specifically I would not have been able to formulate the following clause, "... WITH CHECK (username = SESSION_USER)", w/o first knowing that SESSION_USER was a thing.
Regards,
Dane
On Sat, Jul 11, 2015 at 3:54 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
To add to this. In the documentation section:On 07/11/2015 12:21 PM, Raymond O'Donnell wrote:On 11/07/2015 20:07, Dane Foster wrote:
<snip>As a recent convert to the Church of Postgres I've been consuming vast
Welcome to the One True Faith! :-)amounts of information on PostgreSQL, and SESSION_USER is not the first
nor only, what I'm calling magic constant, that I've seen. Off the top
of my head, other examples that I've encountered are CURRENT_USER and
CURRENT_TIMESTAMP.
So my question is this, is there a reference table in the documentation
that I haven't found yet that lists all magic constants and their
meaning? And if not in the official documentation is it in the wiki?
session_user, current_timestamp and current_user are all functions, not
magic constants:
http://www.postgresql.org/docs/9.4/static/functions-datetime.html
http://www.postgresql.org/docs/9.4/static/functions-info.html
I hope this helps,
http://www.postgresql.org/docs/9.4/interactive/index.html
there is a Search box where you can enter the word/phrase you are looking for.
If all else fails there is the Index:
http://www.postgresql.org/docs/9.4/interactive/index.html
Ray.
--
Adrian Klaver
adrian.klaver@aklaver.com
Hi Dane
There is a list of reserved keywords, including the ones that you mentioned.
http://www.postgresql.org/docs/9.5/static/sql-keywords-appendix.html
However you still need to search for their meaning.
Maybe it helps.
Bye
Charles
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Dane Foster
Sent: Samstag, 11. Juli 2015 22:56
To: Adrian Klaver
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] A table of magic constants
Hello Adrian,
Thank you for the additional reference links but my concern was less about how to find out what a function (formerly magic constant) that I encountered in the wild did but more about having a list that would educate newcomers/me about what is automatically available for use. For example, in the RLS example from my original message, had I the same or similar need as the poster I would not have been able to formulate the policy that I quoted because I had no clue that SESSION_USER even existed. Specifically I would not have been able to formulate the following clause, "... WITH CHECK (username = SESSION_USER)", w/o first knowing that SESSION_USER was a thing.
Regards,
Dane
On Sat, Jul 11, 2015 at 3:54 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 07/11/2015 12:21 PM, Raymond O'Donnell wrote:
On 11/07/2015 20:07, Dane Foster wrote:
<snip>As a recent convert to the Church of Postgres I've been consuming vast
Welcome to the One True Faith! :-)amounts of information on PostgreSQL, and SESSION_USER is not the first
nor only, what I'm calling magic constant, that I've seen. Off the top
of my head, other examples that I've encountered are CURRENT_USER and
CURRENT_TIMESTAMP.
So my question is this, is there a reference table in the documentation
that I haven't found yet that lists all magic constants and their
meaning? And if not in the official documentation is it in the wiki?
session_user, current_timestamp and current_user are all functions, not
magic constants:
http://www.postgresql.org/docs/9.4/static/functions-datetime.html
http://www.postgresql.org/docs/9.4/static/functions-info.html
I hope this helps,
To add to this. In the documentation section:
http://www.postgresql.org/docs/9.4/interactive/index.html
there is a Search box where you can enter the word/phrase you are looking for.
If all else fails there is the Index:
http://www.postgresql.org/docs/9.4/interactive/index.html
Ray.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 07/11/2015 01:55 PM, Dane Foster wrote: > Hello Adrian, > > Thank you for the additional reference links but my concern was less > about how to find out what a function (formerly magic constant) that I > encountered in the wild did but more about having a list that would > educate newcomers/me about what is automatically available for use. For > example, in the RLS example from my original message, had I the same or > similar need as the poster I would not have been able to formulate the > policy that I quoted because I had no clue that SESSION_USER even > existed. Specifically I would not have been able to formulate the > following clause, "... WITH CHECK (username = SESSION_USER)", w/o first > knowing that SESSION_USER was a thing. Well that is a generic problem of how to know what you do not know. Similar to starting out looking for a job when the jobs want job experience. The documentation, as you have found out, is extensive. 1000+ pages the last time I heard someone printing it out. So just reading through it and learning everything is not feasible. I would say learn on a problem by problem basis. Start doing something, go to the section of the docs that deal with that and look at the examples, they tend to illustrate common problems. Next step would be using your favorite search engine and looking up examples, say 'Postgres get current user example'. In DuckDuckGo this has one of the links Ray posted as the first hit. Ask this list. FYI, convention on the list is to bottom post. > > Regards, > > Dane -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, 11 Jul 2015 16:55:44 -0400
Dane Foster <studdugie@gmail.com> wrote:
> Thank you for the additional reference links but my concern was less about
> how to find out what a function (formerly magic constant) that I
> encountered in the wild did but more about having a list that would educate
> newcomers/me about what is automatically available for use.
In addition to what Adrian wrote, I find that the best way is to simply read the messages posted on this list for a
while: you'll learn _a lot_ of things from the answers. After a while, you'll find your way around the documentation.
I've been doing it almost every day for years, still learning every time.
--
Salutations, Vincent Veyron
https://legalcase.libremen.com/
Legal case, contract and insurance claim management software
On Sat, Jul 11, 2015 at 4:53 PM, Vincent Veyron <vv.lists@wanadoo.fr> wrote:
On Sat, 11 Jul 2015 16:55:44 -0400
Dane Foster <studdugie@gmail.com> wrote:
. After a while, you'll find your way around the documentation.
I've been doing it almost every day for years, still learning every time.
I highly recommend reading the documentation from 'cover to cover' periodically. Yes, there will be things you don't understand yet, but each time you'll pick up things you didn't get in previous passes. A lot of people have put in a lot of time on that documentation, and it is first-rate. (I've been working on a project that requires MySQL, their documentation is far inferior.)
--
--
Mike Nolan
Hi Michael,
You nailed it. I am reading the documentation cover to cover. I started chapter 9 two weeks ago but haven't found the time to read beyond 9.1 yet. But for day to day usage on the MySQL to PostgreSQL migration project that I'm working on I jump around in the docs as needed to find specific things that are further ahead in the docs than chapter 9.
Dane
On Sat, Jul 11, 2015 at 6:34 PM, Michael Nolan <htfoot@gmail.com> wrote:
On Sat, Jul 11, 2015 at 4:53 PM, Vincent Veyron <vv.lists@wanadoo.fr> wrote:On Sat, 11 Jul 2015 16:55:44 -0400
Dane Foster <studdugie@gmail.com> wrote:
. After a while, you'll find your way around the documentation.
I've been doing it almost every day for years, still learning every time.I highly recommend reading the documentation from 'cover to cover' periodically. Yes, there will be things you don't understand yet, but each time you'll pick up things you didn't get in previous passes. A lot of people have put in a lot of time on that documentation, and it is first-rate. (I've been working on a project that requires MySQL, their documentation is far inferior.)
--Mike Nolan
Dane Foster wrote: > Hi Michael, > > You nailed it. I am reading the documentation cover to cover. I started > chapter 9 two weeks ago but haven't found the time to read beyond 9.1 yet. > But for day to day usage on the MySQL to PostgreSQL migration project that > I'm working on I jump around in the docs as needed to find specific things > that are further ahead in the docs than chapter 9. FWIW if you find things that would benefit from having an entry in the alphabetical index and are not listed there, by all means report that to pgsql-docs or this list. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services