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);
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.htmlI 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
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.htmlI 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
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> 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 <mailto: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 <mailto: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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
nolan@tssi.com
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:
Show quoted text
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
nolan@tssi.com
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general