Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgadmin-hackers by date

Next:From: Leszek TrenknerDate: 2007-05-09 01:59:28
Subject: Re: build environment
Previous:From: svnDate: 2007-05-08 16:06:31
Subject: SVN Commit by dpage: r6274 - in trunk/pgadmin3/pgadmin: frm include/frm include/utils ui utils

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group