6

In pgAdminIII I create a function that returns multiple rows in a single column named identifier using RETURN TABLE:

CREATE OR REPLACE FUNCTION test(
    IN parm1 date,
    IN parm2 interval)
  RETURNS TABLE(identifier INT) 
AS
$BODY$
BEGIN
    RETURN QUERY
    SELECT 1;

END;
$BODY$
  LANGUAGE plpgsql;

After the function is created, pgAdminIII shows the function as having the following definition:

CREATE OR REPLACE FUNCTION test(IN parm1 date, IN parm2 interval)
  RETURNS SETOF integer AS
$BODY$
BEGIN
    RETURN QUERY
    SELECT 1;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

When executing the function it follows the logic of the RETURNS TABLE. But, when editing the function it will pickup the RETURNS SETOF. This provides different functionality than the function I specified (e.g. the name of the returned column no longer exists).

So, how can I force pgAdminIII to keep the returned column name of identifier? I don't particularly care whether I define it as a TABLE or SETOF.

  • So update the question with this information. Comments are for other things. – Erwin Brandstetter Jul 4 '15 at 3:08
  • @erwin Done. Had a very hard time finding the edit button earlier. – jzacharuk Jul 4 '15 at 14:32
2

What you see is obviously a display bug in the current pgAdmin 1.20. I could reproduce it.

You must be aware that the code you see is re-engineered from system table entries.

If you look up your function in the system catalogs directly, you'll find that your the return type has been registered properly (at least it works for me in pg 9.4):

SELECT pg_get_functiondef(oid)
FROM   pg_proc
WHERE  proname = 'test';

More useful functions like pg_get_functiondef() in the manual.

You might want to ask for this on the pgAdmin support list:

http://www.postgresql.org/list/pgadmin-support/

Mail to pgadmin-support@postgresql.org

How can I force the returned column to have a name of identifier?

Your approach with RETURNS TABLE(identifier int) is the right way. RETURNS SETOF does not take a parameter name, unless you combine it with an OUT parameter. Like so:

CREATE OR REPLACE FUNCTION test(parm1 date
                              , parm2 interval
                              , OUT identifier int)
  RETURNS SETOF integer AS ...
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.