pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Function returning an array
4+ messages / 4 participants
[nested] [flat]

* Function returning an array
@ 2001-08-23 14:58  Francesco Casadei <f_casadei@libero.it>
  0 siblings, 1 reply; 4+ messages in thread

From: Francesco Casadei @ 2001-08-23 14:58 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Function returning an array
@ 2001-08-23 15:44  Josh Berkus <josh@agliodbs.com>
  parent: Francesco Casadei <f_casadei@libero.it>
  0 siblings, 1 reply; 4+ messages in thread

From: Josh Berkus @ 2001-08-23 15:44 UTC (permalink / raw)
  To: Francesco Casadei <f_casadei@libero.it>; +Cc: pgsql-sql

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







^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Function returning an array
@ 2001-08-23 17:07  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2001-08-23 17:07 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Francesco Casadei <f_casadei@libero.it>; pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Function returning an array
@ 2001-08-23 19:45  Alex Pilosov <alex@pilosoft.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Alex Pilosov @ 2001-08-23 19:45 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-08-23 19:45 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-23 14:58 Function returning an array Francesco Casadei <f_casadei@libero.it>
2001-08-23 15:44 ` Josh Berkus <josh@agliodbs.com>
2001-08-23 17:07   ` Tom Lane <tgl@sss.pgh.pa.us>
2001-08-23 19:45     ` Alex Pilosov <alex@pilosoft.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox