From: | Erwin Brandstetter <brandstetter(at)falter(dot)at> |
---|---|
To: | pgadmin-hackers(at)postgresql(dot)org |
Subject: | Name proliferation in functions with OUT parameters |
Date: | 2007-05-08 21:23:10 |
Message-ID: | 4640EA3E.5000701@falter.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-hackers |
Hi developers! Hi Dave!
Testing pgAdmin III v1.6.3 rev: 6112, client Win XP, host: Debian Sarge,
PG 8.1.8.
I got nonsensical error messages when trying to change properties of a
function in the properties dialogue window.
After some investigation I was able to build the following, very simple
testcases that should demonstrate the problem.
1.) 2 unnamed IN parameters, 1 named OUT parameter
CREATE FUNCTION test1(integer, integer, OUT a integer) AS
'SELECT 1, 1;'
LANGUAGE 'sql' VOLATILE;
Now, try changing the volatility to "STABLE" in the properties dialogue
window. It seems to work, but pgAdmin messes with parameter names:
That's the actual command sent back to the database:
CREATE OR REPLACE FUNCTION test1(IN a integer, IN integer, OUT
integer) AS
'SELECT 1;'
LANGUAGE 'sql' STABLE;
Note how the name was "a" was transferred to the first IN parameter.
(The IAEA would call this illegal proliferation!!)
2.) 2 unnamed IN parameters, 2 named OUT parameter
CREATE FUNCTION test2(integer, integer, OUT a integer, OUT b integer) AS
'SELECT 1, 1;'
LANGUAGE 'sql' VOLATILE;
Try changing the volatility to "STABLE" in the properties dialogue
window again. This time it fails with an error message.
The command sent to the database:
CREATE OR REPLACE FUNCTION test2(IN a integer, IN b integer, OUT
integer, OUT integer) AS
'SELECT 1, 1;'
LANGUAGE 'sql' STABLE;
The logged error message:
FEHLER: kann Rückgabetyp einer bestehenden Funktion nicht ändern
DETAIL: Der von OUT-Parametern bestimmte Zeilentyp ist verschieden.
TIPP: Verwenden Sie zuerst DROP FUNCTION.
ANWEISUNG: CREATE OR REPLACE FUNCTION test2(IN a integer, IN b
integer, OUT integer, OUT integer) AS
'SELECT 1, 1;'
LANGUAGE 'sql' STABLE;
Not sure why postgres wouldn't throw an error in the first case. Looks
like the same error to me. Maybe a glitch in postgresql itself?
3.) 1 unnamed IN parameters, 1 named OUT parameter
CREATE FUNCTION test3(IN a integer, integer, OUT b integer) AS
'SELECT 1;'
LANGUAGE 'sql' VOLATILE;
Gets transformed to:
CREATE OR REPLACE FUNCTION test3(IN a integer, IN b integer, OUT
integer) AS
'SELECT 1;'
LANGUAGE 'sql' STABLE;
4.) 2 named IN parameters, 2 named OUT parameter
CREATE FUNCTION test4(IN a integer, IN b integer, OUT a integer, OUT b
integer) AS
'SELECT 1,1;'
LANGUAGE 'sql' VOLATILE;
This example finally works as it should. You get the idea ...
Names of OUT parameters are "shifted" to unnamed IN parameters, one
after the other.
Regards
Erwin
From | Date | Subject | |
---|---|---|---|
Next Message | Leszek Trenkner | 2007-05-09 01:59:28 | Re: build environment |
Previous Message | svn | 2007-05-08 16:06:31 | SVN Commit by dpage: r6274 - in trunk/pgadmin3/pgadmin: frm include/frm include/utils ui utils |