PostgreSQL: Possible to create a function with a TYPE and modify the TYPE after

Question

Hi I want to create a function which returns a user defined type. After the function is created I want to be able to update the type to something else, so that the function uses the new type eg.

CREATE TYPE my_type AS ("Column1" text, "Column2" text);

CREATE OR REPLACE FUNCTION my_fucntion()
RETURNS SETOF my_type
bla
bla
blah

ALTER TYPE my_type AS ("Name" text, "Address" text)

Is this possible? the alterations will always only be the name of the column not the actual data format, that will always be the same.

EDIT: Well the thing is I have 2 scripts one which is generic and must be installed as is with no changes. It has to be installed in multiple places. The second is a sort of config script.

In the generic script I create a function which returns a type of what I wanted to define in the config script.

I thought to install the config script first but I can't as I need to perform some Inserts based on the functions which are in generic script.

Thanks

Daniel Vérité · Accepted Answer · 2012-03-10 01:18:32Z

2

I tried with PostgreSQL 9.1, and it seems to just work. Example:

CREATE TYPE my_type as (col1 text, col2 text);
CREATE TABLE my_table (a my_type);
INSERT INTO my_table values(('ab','cd'));
CREATE FUNCTION my_function() returns setof my_type as 
    'begin return query select (a).* from my_table; end;'
language plpgsql;

select * from my_function();

Output is:

 col1 | col2 
------+------
 ab   | cd

Now let's rename columns in my_type:

alter type my_type rename attribute col1 to address;
alter type my_type rename attribute col2 to name;

=> No error

Now call again the function:

select * from my_function();

No error, output is:

 address | name 
---------+------
 ab      | cd

Looks like the expected result, doesn't it?

answered Mar 10, 2012 at 1:18

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aydin Hassan Over a year ago

We are using version 9.0 and it seems to not work, doubt they'll be any chance of the sysadmin updating it either, thanks though

Daniel Vérité Over a year ago

Indeed, it can't work with PG 9.0 for lack of the RENAME ATTRIBUTE clause in ALTER TYPE (according to the doc: postgresql.org/docs/9.0/static/sql-altertype.html)

Erwin Brandstetter · Accepted Answer · 2012-03-09 18:07:56Z

This is generally impossible in PostgreSQL. I quote the manual on CREATE FUNCTION:

Also, CREATE OR REPLACE FUNCTION will not let you change the return type of an existing function. To do that, you must drop and recreate the function. (When using OUT parameters, that means you cannot change the types of any OUT parameters except by dropping the function.)

Bold emphasis mine. To facilitate the process (and remove the tie between type and function) you might consider using OUT parameters or RETURNS TABLE instead of RETURNS SETOF my_type. Like this:

CREATE OR REPLACE FUNCTION my_function()
 RETURNS TABLE (column1 text, column2 text) AS
$BODY$
 -- do something
$BODY$ language xyz;

As an aside: My personal advise is to always user lower case identifiers in PostgreSQL. I renamed the OUT columns accordingly in the example.

Richard Huxton · Accepted Answer · 2012-03-09 18:56:39Z

1

Can't see how you could get this to work. Anything depending on "my_type" would break when you changed its definition.

Perhaps post another question with the actual problem you are trying to solve.

EDIT (after question clarification): OK - so you want renamed columns for specific customers or sites or some such. Either wrap your original function in a view:

CREATE VIEW local_columns AS
SELECT column1 AS foo, column2 as bar FROM my_function()

If you need to pass parameters, wrap your function in another type and function:

SELECT * FROM my_outer_function('some','params')

Your config file will have to set up the outer type + function or view.

edited Mar 9, 2012 at 18:56

answered Mar 9, 2012 at 14:04

Richard Huxton

23.6k5 gold badges43 silver badges53 bronze badges

2 Comments

Aydin Hassan Over a year ago

Edited, but it seems as though I have an endless loop and I don't think it is going to be possible

Aydin Hassan Over a year ago

I figured something out. I just use this installation process: Install configure script, which creates the types, then in calls the other script, after that's finished then do the inserts

Collectives™ on Stack Overflow

PostgreSQL: Possible to create a function with a TYPE and modify the TYPE after

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related