Postgres functions

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?

From stackoverflow

• 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 ... ;
  

  Markus : Ok, how can I do this using Npgsql, in ado.net?

  Markus : RETURNS SETOFF messages_wall - doesn't work

  Luc M : Sorry, RETURNS SETOF should work.

  Barry Brown : You can't use SETOF messages_wall because that table is joined with the location table, resulting in a table with more columns than messages_wall. You'll need to define a new type that has the appropriate fields in it.

  Markus : It works indeed with r messages_wall%ROWTYPE. But what if I want to return the values from different tables: for r in SELECT messages_wall.id, location.name FROM messages_wall INNER JOIN location ON messages_wall.id = location.id

  Markus : In this case that approach does not work.

  Luc M : @Barry, I didn't remark the JOIN... Only specifying fields and datatypes works.

  Markus : Ok, as I understand, if I specify fields and datatypes, I cannot use ExecuteReader() with command type stored procedure?

• 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.