PostgreSQL function returning a table without specifying each field in a table-CodePudding

I'm moving from SQL server to Postgresql. In SQL Server I can define table-based function as an alias for a query. Example:

Create Function Example(@limit int) As
Returns Table As Return
Select t1.*, t2.*, price * 0.5 discounted
From t1
Inner Join t2 on t1.id = t2.id
Where t1.price < @limit;
GO
Select * From Example(100);

It gives me a way to return all fields and I don't need to specify types for them. I can easily change field types of a table, add new fields, delete fields, and then re-create a function.

So the question is how to do such thing in Postgresql? I found out that Postgresql requires to explicitly specify all field names and types when writing a function. May be I need something else, not a function?

CodePudding user response：

Postgres implicitly creates a type for each table. So, if you are just selecting from one table, it's easiest to use that type in your function definition:

CREATE TABLE test (id int, value int);

CREATE FUNCTION mytest(p_id int) 
RETURNS test AS 
$$ 
SELECT * FROM test WHERE id = p_id;
$$ LANGUAGE SQL;

You are then free to add, remove, or alter columns in test and your function will still return the correct columns.

CodePudding user response：

If you change the function frequently, I'd suggest to use view instead of a function. Because every time you re-create a function, it gets compiled and it's a bit expensive, otherwise you're right - Postgres requires field name and type in functions:

CREATE OR REPLACE VIEW example AS
    SELECT t1.*, t2.*, price * 0.5 discounted
    FROM t1 INNER JOIN t2 ON t1.id = t2.id;

then

SELECT * FROM example WHERE price < 100;

CodePudding user response：

You can do something like this

CREATE OR REPLACE FUNCTION Example(_id int) 
RETURNS RECORD AS
$$
DECLARE 
data_record RECORD;
BEGIN
  SELECT * INTO data_record FROM SomeTable WHERE id = _id;
  RETURN data_record;
END;
$$
LANGUAGE 'plpgsql';