update several columns from function returning several values
Hey,
a trivial question I guess,
can't make it work.
IF I define a function returning several values , I can't use it to update
mutliple columns of a table at once.
i __don't__ want to use CTE or subquerry,
and of course I don't wan tto compute the function several time.
CREATE TABLE test_update_m_values (
gid int,
gid_plus_1 int,
gid_minus_1 int
);
INSERT INTO test_update_m_values VALUES (1,0,0) ;
CREATE OR REPLACE FUNCTION rc_test_update_m_values( gid int,OUT gid_plus_1
int, OUT gid_minus_1 int)
AS $$ -- @brief : test function, can be deleted
BEGIN
SELECT gid+1,gid-1 INTO gid_plus_1, gid_minus_1;
RETURN ;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT ;
UPDATE test_update_m_values SET (gid_plus_1,gid_minus_1) =
(rc_test_update_m_values(gid)); --doesn't work
Somebody now if this is possible?
CHeers,
Rémi-C
=?UTF-8?Q?R=C3=A9mi_Cura?= <remi.cura@gmail.com> writes:
IF I define a function returning several values , I can't use it to update
mutliple columns of a table at once.
...
UPDATE test_update_m_values SET (gid_plus_1,gid_minus_1) =
(rc_test_update_m_values(gid)); --doesn't work
Somebody now if this is possible?
Not currently. In 9.5 it'll be possible to do
UPDATE test_update_m_values SET (gid_plus_1,gid_minus_1) =
(select * from rc_test_update_m_values(gid));
but the syntax you were trying will never work, because it would be
ambiguous with the case of assigning a composite value to a single
composite column.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thanks,
waiting for the 9.5 then =)
Cheers,
Rémi-C
2014-12-02 18:23 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
Show quoted text
=?UTF-8?Q?R=C3=A9mi_Cura?= <remi.cura@gmail.com> writes:
IF I define a function returning several values , I can't use it to
update
mutliple columns of a table at once.
...
UPDATE test_update_m_values SET (gid_plus_1,gid_minus_1) =
(rc_test_update_m_values(gid)); --doesn't workSomebody now if this is possible?
Not currently. In 9.5 it'll be possible to do
UPDATE test_update_m_values SET (gid_plus_1,gid_minus_1) =
(select * from rc_test_update_m_values(gid));but the syntax you were trying will never work, because it would be
ambiguous with the case of assigning a composite value to a single
composite column.regards, tom lane