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 workLuc 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.idMarkus : 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.
it's a bit more introductory