Re: Question about functions that return a set of records

On Fri, Feb 20, 2009 at 3:50 AM, Mike Christensen <imaudi(at)comcast(dot)net> wrote:
> I have the following function:
>
> CREATE FUNCTION foo(_userid uuid)
>  RETURNS SETOF record AS
> $BODY$
> BEGIN
>  RETURN QUERY
>   select n.UserId, u.Alias, n.Date, n.Data
>   --Bunch of joins, etc
>
> If I understand correctly, I have to return "SETOF record" since my result
> set doesn't match a table and isn't a single value.  However, this means
> when I want to call it I have to provide a column definition list, such as:
>
> select * from foo as (...);
>
> Is there any way to specify this column list within the function itself?
>  The problem I'm running into is I want to call this function using Npgsql
> which doesn't appear to support passing in a column definition list.
>

Hmm, Npgsql supports this syntax when your function returns a record.
But I think it is easy to add support for a setof record.
I'll check it out.

To use the support of record in Npgsql, you just need to specify your
parameters which will receive the returned values ("the output list")
as out parameters. Npgsql will take care of them and build the output
list for you when calling your function.

commandtext = "function_name";
command.parameters.add("first parameter"));
command.parameters[0].Direction = InDirection;

command.parameters.add("second parameter"));
command.parameters[1].Direction = OutDirection;

command.parameters.add("Third parameter"));
command.parameters[2].Direction = OutDirection;

And when you call your function, Npgsql will pass your first parameter
and build the output list with the second and third parameters.

For while, if possible, you could use Npgsql support for returning
setof refcursor. You can check examples about how to do that
in our user manual: http://manual.npgsql.org

I hope it helps.

--
Regards,

Francisco Figueiredo Jr.
Npgsql Lead Developer
http://fxjr.blogspot.com
http://www.npgsql.org