CODEWITHSUNDEEP
sqlpostgresqlfunctionplpgsql
Timsen
Timsen

Reputation: 4126

How to alter a composite type when multiple functions are using it?

I am new to PostgreSQL (currently working with MSSQL). Lets assume we have a composite type Customer(uid uuid, name text) and you choose to return this type from your functions (procedures) like:

create or replace function public.getCustomers(value text)
returns setof Customer as 
$$

  select "uid", "name", from "Common_Customer";

$$
language sql;

Everything works and there is no pain, but suddenly you figure out that customer also needs a lastname property to be returned from database.

So you alter the customer type to add an attribute lastname, and that's where the trouble begins. I figured out that I can easily add an attribute to the Customer type, but all functions using it break because they are missing the newly added attribute lastname and there are no default for composite type attributes.

What do you guys do in such a situation? Do you first find all functions using that type, write a query to replace them, then alter the type and replace all the functions in one commit?

In MSSQL there are no types (and you are not forced to tell which type the procedure must return), stored procedures can return exactly what you want them to (which is a problem sometimes). So I am a little blank here.

Upvotes: 1

Views: 299

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658472

I generally prefer RETURNS TABLE(...) instead of SETOF my_composite_type. I only use SETOF where the return type needs to be bound to a common type or an existing table. Example:

That said, once you are where you stand, yes, I would find all functions (and possibly other objects) depending on the composite type and update all of them in a single transaction.

Or some clients (like pgAdmin) list depending objects.

Upvotes: 0

Pavel Stehule
Pavel Stehule

Reputation: 45910

PostgreSQL is strictly typed environment - it is big difference from MSSQL procedures, where you can returns anything. Partially it can be solved with cursors. You can write functions that returns a cursor - and cursor is dynamic.

CREATE OR REPLACE FUNCTION fx()
RETURNS REFCURSOR AS $$
DECLARE refc refcursor;
BEGIN
  OPEN refc FOR SELECT ...;
  RETURN refc;
END;
$$ LANGUAGE plpgsql;

But this technique should be used only when you are doing migration from MSSQL 1:1.

The best practices for PostgreSQL (Oracle is similar) says - don't wrap simple queries by procedures/functions. Use views instead. It doesn't breaking space for query optimization, and you don't need to solve your issues.

The units in SQL are views, not functions.

Functions are necessary for data modification, data checking - but should not to replace a views. Sometimes, when the calculation is too complex, then the SET RETURNING FUNCTIONS are great. But wrapping simple queries is not best practice.

Upvotes: 2

Related Questions