Re: kind of a bag of attributes in a DB . . .

[Albretch Mueller]

 just download a bunch of json info files from youtube data Feeds

 Actually, does postgresql has a json Driver of import feature?

 the metadata contained in json files would require more than one
small databases, but such an import feature should be trivial

 C

Re: Cascade Trigger Not Firing

[Adrian Klaver]

On 9/13/19 8:07 PM, Judy Loomis wrote:
At the very least that note about this behavior should be highlighted, 
probably on the Trigger Behavior page and not buried in a bunch of notes 
on the Create Trigger page.


I know this after the fact. Still, as a general rule the best place to 
start when learning about a command is on it's respective page under here:


https://www.postgresql.org/docs/11/sql-commands.html

Further I usually go to the Notes after reading the synopsis, as Notes 
is where the exceptions to the rules and gotchas are called out.






On Fri, Sep 13, 2019 at 4:03 PM Tom Lane > wrote:


Judy Loomis mailto:hoodie.j...@gmail.com>>
writes:
 > I'm going to have to really look at all my BEFORE UPDATE triggers
and make
 > sure we're not missing any more.
 > And I have to stop telling management that a trigger means we
always know
 > when a value changes.

Well, you can rely on that, just not like this.  Use an AFTER trigger
(else, you can't be sure it fires after all the BEFORE triggers)
and instead of triggering it with a column parameter, have it do
something like "if old.col is distinct from new.col".

Yeah, it's a bit slower that way, but there's no free lunch,
especially if you don't trust your other triggers.  (Although,
if you have so many triggers that that's a problem, I think you
might have some other design issues.)

                         regards, tom lane



--

--
*Judy Loomis*
469.235.5839



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: kind of a bag of attributes in a DB . . .

[Adrian Klaver]

On 9/14/19 2:06 AM, Albretch Mueller wrote:

  just download a bunch of json info files from youtube data Feeds

  Actually, does postgresql has a json Driver of import feature?


Not sure what you mean by above?

Postgres has json(b) data types that you can import JSON into:

https://www.postgresql.org/docs/11/datatype-json.html


  the metadata contained in json files would require more than one
small databases, but such an import feature should be trivial


Again, not sure I understand why small databases are required?



  C




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: kind of a bag of attributes in a DB . . .

[Adrian Klaver]

On 9/14/19 2:06 AM, Albretch Mueller wrote:

  just download a bunch of json info files from youtube data Feeds

  Actually, does postgresql has a json Driver of import feature?


I'm working without a net(coffee) and so I forgot to mention that for 
Python there is:


http://initd.org/psycopg/docs/extras.html?highlight=json

Not sure if this is what you are looking for or not.



  the metadata contained in json files would require more than one
small databases, but such an import feature should be trivial

  C




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Cascade Trigger Not Firing

[Tom Lane]

Adrian Klaver  writes:
> On 9/13/19 8:07 PM, Judy Loomis wrote:
>> At the very least that note about this behavior should be highlighted, 
>> probably on the Trigger Behavior page and not buried in a bunch of notes 
>> on the Create Trigger page.

> I know this after the fact. Still, as a general rule the best place to 
> start when learning about a command is on it's respective page under here:
> https://www.postgresql.org/docs/11/sql-commands.html
> Further I usually go to the Notes after reading the synopsis, as Notes 
> is where the exceptions to the rules and gotchas are called out.

The only mention of this feature in 38.1 "Overview of Trigger Behavior"
is 

UPDATE triggers can moreover be set to fire only if certain columns
are mentioned in the SET clause of the UPDATE statement.

which seems to me to be plenty specific enough --- it is carefully
*not* saying that the trigger will fire if the column changes value.
The CREATE TRIGGER man page never says that, either.

regards, tom lane

Re: Cascade Trigger Not Firing

[Ron]

On 9/14/19 9:54 AM, Tom Lane wrote:
[snip

The only mention of this feature in 38.1 "Overview of Trigger Behavior"
is

 UPDATE triggers*can*  moreover be set to fire only if certain columns
 are mentioned in the SET clause of the UPDATE statement.

which seems to me to be plenty specific enough --- it is carefully
*not* saying that the trigger will fire if the column changes value.
The CREATE TRIGGER man page never says that, either.


Given that the UPDATE "*can* ... be set to fire only if certain columns are 
mentioned in the SET clause of the UPDATE statement", it logically follows 
that the default behavior is something else (for example, if the field value 
changes for whatever reason.


--
Angular momentum makes the world go 'round.

Can I wrtie a function that has a BEFORE trigger that is not column name dpendent?

[stan]

I would like to write a generic function that I can place as a BEFORE trigger
on several tables. It would do a max() on the column it was triggered for, and
return(max + 1) unless max returns a NULL, in which case it would return one.

Yes, I know this  looks a lot like a sequence, but normally this value would
be provided manually at row input time, I just want to allow for some
automated updates.

The trick is hat the function needs to work for any numeric column I place it n
as a trigger.


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: Can I wrtie a function that has a BEFORE trigger that is not column name dpendent?

[Adrian Klaver]

On 9/14/19 11:33 AM, stan wrote:

I would like to write a generic function that I can place as a BEFORE trigger
on several tables. It would do a max() on the column it was triggered for, and
return(max + 1) unless max returns a NULL, in which case it would return one.

Yes, I know this  looks a lot like a sequence, but normally this value would
be provided manually at row input time, I just want to allow for some
automated updates.

The trick is hat the function needs to work for any numeric column I place it n
as a trigger.



What procedural language?

Triggers are placed on tables not columns so you will need some way of 
identifying the column(s) in the table. Trigger functions can take 
arguments even though they are not declared in the function creation. So 
that is one way of defining the column.







--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Can I wrtie a function that has a BEFORE trigger that is not column name dpendent?

[Adrian Klaver]

On 9/14/19 12:44 PM, stan wrote:

Please reply to list also.
Ccing list.


On Sat, Sep 14, 2019 at 11:50:08AM -0700, Adrian Klaver wrote:

On 9/14/19 11:33 AM, stan wrote:

I would like to write a generic function that I can place as a BEFORE trigger
on several tables. It would do a max() on the column it was triggered for, and
return(max + 1) unless max returns a NULL, in which case it would return one.

Yes, I know this  looks a lot like a sequence, but normally this value would
be provided manually at row input time, I just want to allow for some
automated updates.

The trick is hat the function needs to work for any numeric column I place it n
as a trigger.



What procedural language?

Triggers are placed on tables not columns so you will need some way of
identifying the column(s) in the table. Trigger functions can take arguments
even though they are not declared in the function creation. So that is one
way of defining the column.



OK, so maybe I cna declare trigers that are specfic to the table/column, and
use them to pass the column name to the function.


A trigger is specific to a table. The function it invokes can accept
arguments that help narrow it's focus.

It is spelled out here:

https://www.postgresql.org/docs/11/sql-createtrigger.html

and if you are using plpgsql here:

https://www.postgresql.org/docs/11/plpgsql-trigger.html

To build dynamic SQL that operates on the arguments or other table 
specific operations you can use(again plpgsql specific):


https://www.postgresql.org/docs/11/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN



--
Adrian Klaver
adrian.kla...@aklaver.com