Question 114949 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

3

Answers


  1. 0

    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';
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search