Horton
Menu

Thread

  1. A table of magic constants

    Dane Foster <studdugie@gmail.com> — 2015-07-11T19:07:52Z

    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

  2. Re: A table of magic constants

    Raymond O'Donnell <rod@iol.ie> — 2015-07-11T19:21:29Z

    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

  3. Re: A table of magic constants

    Adrian Klaver <adrian.klaver@aklaver.com> — 2015-07-11T19:54:34Z

    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

  4. Re: A table of magic constants

    Dane Foster <studdugie@gmail.com> — 2015-07-11T20:55:44Z

    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
>

  5. Re: A table of magic constants

    Charles Clavadetscher <clavadetscher@swisspug.org> — 2015-07-11T21:14:10Z

    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>

  6. Re: A table of magic constants

    Adrian Klaver <adrian.klaver@aklaver.com> — 2015-07-11T21:26:38Z

    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

  7. Re: A table of magic constants

    Vincent Veyron <vv.lists@wanadoo.fr> — 2015-07-11T21:53:41Z

    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

  8. Re: A table of magic constants

    Michael Nolan <htfoot@gmail.com> — 2015-07-11T22:34:06Z

    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

  9. Re: A table of magic constants

    Dane Foster <studdugie@gmail.com> — 2015-07-11T22:52:11Z

    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
> nolan@tssi.com
>

  10. Re: A table of magic constants

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2015-07-13T14:37:10Z

    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