CODEWITHSUNDEEP
c#sqlpostgresqldapperpsql
Timsen
Timsen

Reputation: 4126

How to handle changes in composite type in postgreSQL

I am about to learn postgresql and i ran in some kind of a problem. I want to have most of my connection from website to db through stored procedure(functions). I have about 20 functions so far where 5 of those functions returned exactly the same table with columns i.e: 

create or replace function my_Function( t text )
returns setof table(uid uuid,testText text, randomInt int ...)
...

since table(uid uuid,testText text, randomInt int ...) repeated itself 5 times i figured out that i could use composite type to return the same type of data from my functions, so if i need to return an extra column i could just change my composite type and voila, all 5 functions should return same columns.

Unfortunately thats not how that works, as soon as i added a new column to my composite type all the functions which used my composite type broke with saem error: 

Final statement returns too few columns

which makes sense because i dont select an extra column to fit the composite type.

Are there any way to tell which functions use a composite type as return type? Are there any way to force composite type to fill in blanks for newly created column (if not, then i can as well use the table(...) return type, because it dont solve the redundancy problem which i try to avoid)?

Upvotes: 1

Views: 1182

Answers (2)

bbsimonbb
bbsimonbb

Reputation: 29002

What advantage do you gain by using Postgres composite types? It seems like a world of pain. Can I explore how this would work with QueryFirst? Your procedures would be in .sql files in your app, versioned with your app, source controlled. For the 5 procedures that share a return type, you would create a C# interface. In each of the 5 Results.cs files, on the results partial class, inherit the interface. As such, the 5 procs could evolve separately, adding columns as needed. If ever you modified a proc such that it no longer fulfilled the interface: compile errors. A much more forgiving, change-tolerant scenario.

disclaimer: I wrote QueryFirst

Upvotes: 0

klin
klin

Reputation: 121644

Example setup:

create type my_type as (id int, name text);

create or replace function my_function_1()
returns setof my_type language plpgsql as $$
declare
    rec my_type;
begin
    rec.id := 1;
    rec.name := 'name';
    return next rec;
end $$;

create or replace function my_function_2()
returns setof my_type language plpgsql as $$
begin
    return query select 1::int, 'name'::text;
end $$;

Are there any way to tell which functions use a composite type as return type?

Yes:

select nspname as schema_name, proname as function_name
from pg_proc p
join pg_namespace n on n.oid = pronamespace
join pg_type t on t.oid = prorettype
where typname = 'my_type';

 schema_name | function_name 
-------------+---------------
 public      | my_function_1
 public      | my_function_2
(2 rows)

Are there any way to force composite type to fill in blanks for newly created column?

No. You could try to recreate (without modifications) the functions after adding a new attribute to the type:

alter type my_type add attribute new_col int;
create or replace function my_function_1() ...
create or replace function my_function_2() ...

But only the functions which return next variable of the type will work properly (my_function_1() will work, my_function_2() won't).

Upvotes: 2

Related Questions