PL/PGSQL rowtype return problem

["Rob Wickert" <robwickert@xxxxxxxxx>]

Hi,

 

I have a problem when I create a function that returns a rowtype. I can't access the individual fields of that rowtype from another function if I put the results in a row type variable.

 

I'm inserting many records into a table (let's say, insert_table). This table has a trigger on it since I need to perform some operations on each row I insert. These operations need to have default information that's stored in another table (let's say, default_values).  To avoid this query getting called for every row (ie. inserting 1,000,000 records into this insert_table), I'd like to create a function that returns a rowtype result from this default_table then declare it immutable so it will only get executed once and then store the result. For example:

 

defaults stored in: default_values

 

CREATE OR REPLACE FUNCTION get_default_values()
  RETURNS default_values AS
$BODY$
DECLARE

      ret_record default_values;

BEGIN

 

SELECT * INTO ret_record
FROM default_values;

RETURN ret_record;

END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE STRICT;

now on the trigger to the insert table we have a function that get's called before every insert.

CREATE OR REPLACE FUNCTION insert_table_fn()
  RETURNS "trigger" AS
$BODY$
DECLARE
     defaults default_values;
BEGIN

 SELECT get_default_values() INTO defaults;
 
 NEW.f1 = defaults.f1;

 NEW.f2 = defaults.f2;

 NEW.f3 = defaults.f3;

 RETURN NEW;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

 

When I run this it says that there's a type mismatch and gives me the whole rowtype back

ERROR: invalid input syntax for integer: "(val1, val2, val3, val4)"
SQL state: 22P02

 

Now if I go NEW.f1 = SELECT (get_default_values()).f1 this works.  But then I have a write this function out in full every time I want to get one of it's fields. ie 

 NEW.f1 = SELECT (get_default_values()).f1;

 NEW.f2 = SELECT (get_default_values()).f2;

 NEW.f3 = SELECT (get_default_values()).f3;

 

Why can't I declare a rowtype variable, put the result in and then grab the individual field types from that variable.

Thanks for your help

Robert