CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Aydin Hassan
Aydin Hassan

Reputation: 1475

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

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

Upvotes: 1

Views: 5005

Answers (3)

Daniel Vérité
Daniel Vérité

Reputation: 61656

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?

Upvotes: 2

Richard Huxton
Richard Huxton

Reputation: 22952

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.

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658372

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.

Upvotes: 1

Related Questions