October 19th, 2012, 06:20 AM
|
|
Registered User
|
|
Join Date: Oct 2012
Posts: 1
Time spent in forums: 1 h 29 m 8 sec
Reputation Power: 0
|
|
|
Postgres behaviour
Below are two examples for which I have a few questions.
First example is about getting values from function returning RECORD type:
Code:
CREATE FUNCTION pg_temp.functest( OUT x FLOAT8, OUT y FLOAT8 ) RETURNS RECORD AS $$
DECLARE
n FLOAT8 := random();
BEGIN
x = n;
y = n;
END;
$$ LANGUAGE PLPGSQL VOLATILE;
SELECT pg_temp.functest();
functest
-------------------------------------
(0.15483684046194,0.15483684046194)
(1 row)
SELECT * FROM pg_temp.functest();
x | y
-------------------+-------------------
0.709271687082946 | 0.709271687082946
(1 row)
SELECT (pg_temp.functest()).*;
x | y
-------------------+-------------------
0.851860670372844 | 0.687716367188841
(1 row)
At the last SELECT function is called twice (which I think is wrong). And my question is this correct behaviour and why is function called N times where N is number of 'columns' in RECORD.
Second example is RULE based example and it is again about calling funcion(s):
Code:
CREATE TEMPORARY TABLE tmp1 ( id INTEGER, val FLOAT8 );
CREATE TEMPORARY TABLE tmp2 ( id INTEGER, val FLOAT8 );
CREATE OR REPLACE RULE tmp1_update_rule
AS ON UPDATE TO tmp1
WHERE (NEW IS DISTINCT FROM OLD)
DO ALSO (
UPDATE tmp2
SET val = NEW.val
WHERE id = NEW.id;
);
CREATE OR REPLACE FUNCTION pg_temp.tmpfun() RETURNS FLOAT8 AS $$
DECLARE
x FLOAT8 = random();
BEGIN
RAISE NOTICE 'Random value: %', x;
RETURN x;
END;
$$ LANGUAGE PLPGSQL VOLATILE;
INSERT INTO tmp1 VALUES (1,10);
INSERT 0 1
INSERT INTO tmp2 VALUES (1,NULL);
INSERT 0 1
UPDATE tmp1 SET val = pg_temp.tmpfun() WHERE id=1;
NOTICE: Random value: 0.0833259411156178
NOTICE: Random value: 0.0810711048543453
NOTICE: Random value: 0.558921546209604
UPDATE 1
SELECT * FROM tmp1;
id | val
----+-------------------
1 | 0.558921546209604
(1 row)
SELECT * FROM tmp2;
id | val
----+--------------------
1 | 0.0810711048543453
(1 row)
And again the question is why is function called three times and why is 'val' from table tmp1 and tmp2 different (which I think it should not be)?
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
