CODEWITHSUNDEEP
sqlpostgresql
Markus
Markus

Reputation: 3627

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?

Upvotes: 2

Views: 4426

Answers (3)

Sebeto
Sebeto

Reputation: 31

SELECT * FROM test2() AS tabletest2 (id integer, name text); if you really wanna use a function, but a view is better in this case

Upvotes: 3

Milen A. Radev
Milen A. Radev

Reputation: 62583

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.

Upvotes: 1

Luc M
Luc M

Reputation: 17314

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

Upvotes: 2

Related Questions