pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction 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