Thread: Function returning an array

I want a function to return an array of two VARCHAR(255).

This works, but does not return VARCHAR(255):
CREATE FUNCTION ins_div(CHAR(8))
RETURNS _varchar
AS '...'
LANGUAGE 'plpgsql';

These don't work:
CREATE FUNCTION ins_div(CHAR(8))
RETURNS _varchar(255)
AS '...'
LANGUAGE 'plpgsql';

CREATE FUNCTION ins_div(CHAR(8))
RETURNS varchar(255)[2]
AS '...'
LANGUAGE 'plpgsql';

CREATE FUNCTION ins_div(CHAR(8))
RETURNS varchar[2](255)
AS '...'
LANGUAGE 'plpgsql';

CREATE FUNCTION ins_div(CHAR(8))
RETURNS setof varchar(255)
AS '..'
LANGUAGE 'plpgsql';

What's the correct syntax to return two VARCHAR(255)?
Francesco Casadei

Ciao Francesco:


> What's the correct syntax to return two VARCHAR(255)?

There isn't one.  At this time, functions cannot return arrays.  Look
for a fix to this limitation with version 7.2 or later.

Jan Wieck and others have been working to extend functions so that they
may manipulate and return cursors.  However, that functionality is still
in development and I don't know whether it will be included in the next
release.

In the meantime, I suggest that you take one of the following courses:

1. Delimit or tokenize your function output and parse it on the
receiving end into 2 fields (e.g. 'element 1|element2')
2. Do the operation in your middleware, where you can use C, Java,
Python or similar to pass arrays.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

"Josh Berkus" <josh@agliodbs.com> writes:
>> What's the correct syntax to return two VARCHAR(255)?

> There isn't one.  At this time, functions cannot return arrays.

Well, they *can*, it's just that there's no syntax to support
constructing an array value in SQL or plpgsql languages.

I know that you can do it in pltcl (there are examples in the pltcl
self-test), and of course you can do it in C.  A brute-force solution
is to make a support function in one of those languages that takes two
varchars and returns an array of varchar.
        regards, tom lane

On Thu, 23 Aug 2001, Tom Lane wrote:

> "Josh Berkus" <josh@agliodbs.com> writes:
> >> What's the correct syntax to return two VARCHAR(255)?
> 
> > There isn't one.  At this time, functions cannot return arrays.
> 
> Well, they *can*, it's just that there's no syntax to support
> constructing an array value in SQL or plpgsql languages.
Most importantly, there's no syntax to support deconstructing an array ;)

I have patches to support "select * from function(args)" almost done, I
just need to perform merge against -current. But unfortunately RL took
most of my time and I was unable to work on pg-related things for last
months. The patches contain quite a lot of changes to core structures
(RangeTblEntry and others) and I want to get them in before 7.2 gets
frozen. Hopefully this weekend I'll have some time to do merge and
cleanup....

Also included in the patch is ability to do 'select * from cursor foo'.


-alex