From: | "Rob Wickert" <robwickert(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | PL/PGSQL rowtype return problem |
Date: | 2007-06-12 12:09:42 |
Message-ID: | 75c6daf90706120509i5327092ama2627505b2bd7f8@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | rwickert | 2007-06-12 12:10:56 | PL/PGSQL rowtype return problem |
Previous Message | Tom Allison | 2007-06-12 10:04:35 | Re: When should I worry? |