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?

Daniel Haley · Accepted Answer · 2011-06-23 02:38:16Z

3

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

edited Jun 23, 2011 at 2:38

Daniel Haley

53k7 gold badges75 silver badges97 bronze badges

answered Jun 22, 2011 at 2:41

Sebeto

312 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Luc M · Accepted Answer · 2009-04-12 19:32:05Z

2

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

edited Apr 12, 2009 at 19:32

answered Apr 12, 2009 at 18:59

Luc M

17.5k26 gold badges78 silver badges91 bronze badges

5 Comments

Markus Over a year ago

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

Barry Brown Over a year ago

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 Over a year ago

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

Luc M Over a year ago

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

Markus Over a year ago

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

Milen A. Radev · Accepted Answer · 2009-04-13 01:56:34Z

1

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.

answered Apr 13, 2009 at 1:56

Milen A. Radev

63.1k22 gold badges111 silver badges112 bronze badges

Collectives™ on Stack Overflow

Postgres functions

3 Answers 3

Comments

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Linked

Related