Thread: how to return more than 1 arg with a function ?

....
I want to return mre than 1 arg

create function func(varchar)
--> returns var1, var2 ....
as '
...
'
language 'sql';

How to deal with the return : The manual says to use SETOF can't figure
out how that works no examples are given.

Feite

Feite Brekeveld wrote:
>
> I want to return mre than 1 arg
>
> create function func(varchar)
> --> returns var1, var2 ....
> as '
> ...
> '
> language 'sql';
>
> How to deal with the return : The manual says to use SETOF can't figure
> out how that works no examples are given.

Not quite right. SETOF returns many items so you could have:

create function list_ids() returns setof int4 as 'select id from foo;'
language 'sql';

select list_ids();

list_id
-------
1
2
7
12

You can also return a tuple like:

create function whole_row() returns foo as ...

But - that can only be used to feed into another function, not in a
"raw" select statement.

HTH

- Richard Huxton

Richard Huxton <dev@archonet.com> writes:
> You can also return a tuple like:
> create function whole_row() returns foo as ...
> But - that can only be used to feed into another function, not in a
> "raw" select statement.

You can make a function returning tuple (or setof tuple), but there
are a whole bunch of silly little problems that restrict what you
can actually *do* with it.  What it comes down to is that you probably
can't do anything useful except select a single field out of the tuple
result per function call, which leaves you wondering why you bothered.
The PostQUEL-derived syntax is bizarre, too: table.field.function.field
(where table.field is the function argument, and the second fieldname
is what you're selecting out of the function result).

We have thoughts of allowing functions returning tuples to be used as
table sources (FROM items), which would overcome the notational issues.
Maybe in 7.2 ...

            regards, tom lane