Обсуждение: A table of magic constants

Поиск
Список
Период
Сортировка

A table of magic constants

От
Dane Foster
Дата:
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

Re: A table of magic constants

От
Raymond O'Donnell
Дата:
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


Re: A table of magic constants

От
Adrian Klaver
Дата:
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


Re: A table of magic constants

От
Dane Foster
Дата:
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

Re: A table of magic constants

От
"Charles Clavadetscher"
Дата:

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

 

Re: A table of magic constants

От
Adrian Klaver
Дата:
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


Re: A table of magic constants

От
Vincent Veyron
Дата:
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


Re: A table of magic constants

От
Michael Nolan
Дата:


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

Re: A table of magic constants

От
Dane Foster
Дата:
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

Re: A table of magic constants

От
Alvaro Herrera
Дата:
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