Postgres functions

Question

I need to make a select query with joins. This should be written in a function. My approach didn't work:

CREATE OR REPLACE FUNCTION test2()
RETURNS SETOF record AS'
DECLARE
    r record;
BEGIN
    for r in SELECT * FROM messages_wall INNER JOIN location ON
         messages_wall.id = location.id
         loop
    return next r;
    end loop;
end; '
LANGUAGE 'plpgsql'

ERROR: a column definition list is required for functions returning "record"

I should call this function from a .net application. How should I proceed?

Answer 1

Instead joining table into your function, you may use view.

RETURNS SETOF your_view;

You probably have to define r with the appropriate datatype too:

r your_view%ROWTYPE;

or

You have to specify the fields and datatype.

SELECT * FROM test2() AS fields_1 as INTEGER, fields_2 AS ... ;

Answer 2

For your example wrapping a join in a (PL/pgSQL) function is unnecessary. As Luc M has mentioned a view would be simplest and fastest.

If you insist on having a function though your next choice should be an SQL function - more info here and here.