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

Question

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?

The type of SomeTable.* is SomeTable. If you use the TABLE(…) keyword, you have to specify the columns. — Bergi
– Bergi, Commented Aug 27, 2022 at 12:37

Jeremy · Accepted Answer · 2022-08-29 12:23:08Z

6

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.

EDIT: The question was updated to use the function parameter in the limit clause and to use a more complex query. I would still recommend a similar approach, but you could use a view as @Bergi recommends:

CREATE TABLE test1 (a int, b int);
CREATE TABLE test2 (a int, c int);
CREATE VIEW test_view as SELECT a, b, c from test1 JOIN test2 USING (a);

CREATE FUNCTION mytest(p_limit int)
RETURNS SETOF test_view AS
$$
SELECT * FROM test_view FETCH FIRST p_limit ROWS ONLY
$$ LANGUAGE SQL;

You aren't going to find an exact replacement for the behavior in SQL Server, it's just not how Postgres works.

edited Aug 29, 2022 at 12:23

answered Aug 27, 2022 at 12:35

Jeremy

6,77322 silver badges21 bronze badges

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

3 Comments

Raider Over a year ago

Thanks for your answer. But how to deal with SELECT *, some, more, fields FROM test? or SELECT t1.*, t2.*, some, more, fields FROM t1 INNER JOIN t2?

Bergi Over a year ago

@Raider Then you should consider using a view, or you will have to spell out the columns

Jeremy Over a year ago

@Raider I updated my answer to show how you can use a view.

Saya · Accepted Answer · 2022-08-27 19:02:32Z

3

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;

answered Aug 27, 2022 at 19:02

Saya

3632 silver badges8 bronze badges

1 Comment

Raider Over a year ago

View doesn't match my requirements, because my queries are more complex, like Select ... From ... Outer Apply (Select Top 1 From ... Where Date < @Limit Order By Date Desc) there the @Limit is a function parameter

IVO GELOV · Accepted Answer · 2022-08-27 11:25:54Z

-1

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';

answered Aug 27, 2022 at 11:25

IVO GELOV

14.3k1 gold badge21 silver badges30 bronze badges

2 Comments

Raider Over a year ago

I get an error trying to select values from this function: "SQL Error [42601]: ERROR: a column definition list is required for functions returning "record" Position: 15 ERROR: a column definition list is required for functions returning "record" Position: 15 ERROR: a column definition list is required for functions returning "record" Position: 15

Jeremy Over a year ago

records are flexible, but difficult to deal with. You do need to provide a column definition list when using them.

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related