Re: Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT - Mailing list pgsql-general

From Charles Clavadetscher
Subject Re: Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT
Date
Msg-id 019001d0e0cf$a17006b0$e4501410$@swisspug.org
Whole thread Raw
In response to Re: Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
Hi

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Adrian Klaver
> Sent: Donnerstag, 27. August 2015 15:41
> To: Christopher BROWN <brown@reflexe.fr>
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Strange TRIGGER failure with FOR ... IN ... LOOP ...
> INSERT
>
> On 08/27/2015 06:33 AM, Christopher BROWN wrote:
> > Hello Adrian,
> >
> > Yep, Charles' explanation helped me understand what was going on.
> > Before that, I was as confused as you were (in your first reply) about
> > how access_mode could be NULL (with the same reasoning).  In any case,
> > thanks for your links ; I did try searching the web for the answer
> > before posting, but got too many irrelevant results given that I had to
> > search using very common terms.
>
> Yeah, I did not get Charles's second post until I sent my second, so it
> was redundant.
>
> >
> > I've concluded the the RECORD type is the best-fit for my approach.  I
> > don't know if it's any faster that using SELECT * with a specific
> > %ROWTYPE given that the data doesn't go anywhere outside the function
> > body.  I don't know if the order in which columns are returned (by
> > either SELECT * or using explicit column names matters when using
> > %ROWTYPE), although I'll assume that PostgreSQL is smart enough to match
> > things up correctly, if I need to write a function that returns
> > instances of any given %ROWTYPE in the future.
>
> I don't know, I have always just used SELECT * as I needed all the
> columns anyway.

It is probably a matter of taste, more than best practices. I find the variant with RECORD better, because you only
searchfor the fields that you need. In this case it may make a little difference, but if you happen to have a table
withmany more columns, it would be a waste of resources, unless, as in Adrian's case, you do need all the fields. While
usinga rowtype then SELECT * is guaranteed, IMHO, to return that record's fields in the correct order. Listing the
fieldsexplicitly, as you already noticed, could lead to a maintenance nightmare if anything changes in the table
structure.In both cases you are still not safe against, e.g. changes of column names. But I guess that this is not an
issueso far. 

Bye
Charles




pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT
Next
From: Adrian Klaver
Date:
Subject: Re: Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT