Name proliferation in functions with OUT parameters

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

Responses

Browse pgadmin-hackers by date

  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