Thread: PL/PGSQL rowtype return problem

Hi,
I have a problem when I create a function that returns a rowtype. I can't access the individual fields of that rowtype
fromanother 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
performsome 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
intothis insert_table), I'd like to create a function that returns a rowtype result from this default_table then
declareit 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
everytime 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
*****************************************************
Robert Wickert
Senior Software Developer
CONTEXT
Tel:+44 (0)20 8394 7739
Fax:+44 (0)20 8394 7701
Email Address: rwickert@contextworld.com
Web Site: http://www.contextworld.com
*****************************************************
This message and the information contained therein is intended for the use
of the person(s) ("the intended  recipient(s)" to whom it is addressed. It
may contain information that is privileged and confidential within the
meaning of applicable law. If you are not the intended recipient, please
contact the sender as soon possible. The views expressed in this
communication may not necessarily  be the views held by Context or its
subsidiaries. The contents of an attachment to this e-mail may contain
viruses that could damage your own computer system. While every reasonable
precaution has been taken to minimise this risk, Context and its
subsidiaries cannot  accept liability for any damage which you sustain  as a
result of software viruses. You should carry out your own virus checks
before opening the attachment. Please notice that Context monitors e-mails
sent or received. Further communication will signify your consent to this.

Hello,

my code works well:

CREATE TABLE Foo(a integer, b integer);

CREATE OR REPLACE FUNCTION ret_foo()
RETURNS Foo AS $$
DECLARE r Foo;
BEGIN
  r := (10,20); --default
  RETURN r;
END; $$
LANGUAGE plpgsql;

SELECT ret_foo();

CREATE OR REPLACE FUNCTION trig()
RETURNS TRIGGER AS $$
BEGIN
  NEW := ret_foo();
  RETURN NEW;
END; $$
LANGUAGE plpgsql;

CREATE TRIGGER footrig BEFORE INSERT ON Foo
  FOR EACH ROW EXECUTE PROCEDURE trig();

INSERT INTO Foo VALUES(0,0);

Regards
Pavel Stehule

rwickert@contextworld.com writes:
> I have a problem when I create a function that returns a rowtype. I can't a=
> ccess the individual fields of that rowtype from another function if I put =
> the results in a row type variable.

I think this is just a scalar assignment:

> SELECT get_default_values() INTO defaults;

This might work better:

SELECT * INTO defaults FROM get_default_values();

            regards, tom lane