Re: 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.
>
> 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.

Order does matter:

create table rowtype_test(id int, fld_1 varchar, fld_2 varchar);

insert into rowtype_test values (1, 'one', 'two');
insert into rowtype_test values (2, 'three', 'four');

CREATE OR REPLACE FUNCTION row_type_test ( )
  RETURNS void
  LANGUAGE plpgsql

AS $function$
DECLARE
     r rowtype_test%rowtype;
BEGIN
     FOR r IN
         SELECT fld_1, id, fld_2 FROM rowtype_test
     LOOP
         RAISE NOTICE '%', r;
     END LOOP;
     RETURN;
END;
$function$
;


test=> select row_type_test();
ERROR:  invalid input syntax for integer: "one"
CONTEXT:  PL/pgSQL function row_type_test() line 5 at FOR over SELECT rows



>
> Thanks again.
> Christopher
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com