By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,812 Members | 1,978 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,812 IT Pros & Developers. It's quick & easy.

passing array as argument and returning an array in plpgsql

P: n/a
Hi all,
I am using postgresql7.4. How to handle arrays in plpgsql. How can
I pass an array. Is it possible to retrieve values from an array by
indexing it like

argument : '{1,2,3}'
Return value : varchar array

Variables :
---------

a alias for $1
b _varchar

Usage :
-----

b[1] = a[1];
b[2] = a[2];

return b;

Is it possible.

TIA,

--
regards,
Deepa K

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
hello

It is possible

CREATE OR REPLACE FUNCTION foo(anyarray) RETURNS anyarray AS '
DECLARE b integer[];
BEGIN b := $1; b[1] := b[1] + 1;
RETURN b;
END;
' LANGUAGE plpgsql;

testdb011=> select foo(ARRAY[1,2,3]);
foo
---------
{2,2,3}
(1 dka)

Regards
Pavel
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #2

P: n/a
I got this when I was searching for something else. I will forward this
to you.
http://archives.postgresql.org/pgsql...1/msg00852.php
CREATE or REPLACE FUNCTION foo(integer[]) RETURNS int AS
'DECLARE
a alias for $1;
index integer := 1;
total integer := 0;
BEGIN
WHILE a[index] > 0
LOOP
total := total + a[index];
index := index + 1;
END LOOP;

RETURN total;
END;
' LANGUAGE 'plpgsql';

test=> select foo('{1,2}');
foo
-----
3
(1 row)
On Tue, 2003-12-16 at 03:25, K. Deepa wrote:
Hi all,
I am using postgresql7.4. How to handle arrays in plpgsql. How can
I pass an array. Is it possible to retrieve values from an array by
indexing it like

argument : '{1,2,3}'
Return value : varchar array

Variables :
---------

a alias for $1
b _varchar

Usage :
-----

b[1] = a[1];
b[2] = a[2];

return b;

Is it possible.

TIA,

--
Jenny Zhang
Open Source Development Lab
12725 SW Millikan Way, Suite 400
Beaverton, OR 97005
(503)626-2455 ext 31

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #3

P: n/a
Jenny Zhang wrote:
Jenny,

although this is old but i find it worth mentioning tom's
comment on it. I hit your comment on facing similar issue.

The cited example is pretty iffy since it assumes that the valid array
entries are all > 0. In recent PG version you can use the array_upper
and array_lower functions instead:

for i in array_lower(a,1) .. array_upper(a,1) loop
-- do something with a[i]
end loop;

regards, tom lane


I got this when I was searching for something else. I will forward this
to you.
http://archives.postgresql.org/pgsql...1/msg00852.php
CREATE or REPLACE FUNCTION foo(integer[]) RETURNS int AS
'DECLARE
a alias for $1;
index integer := 1;
total integer := 0;
BEGIN
WHILE a[index] > 0
LOOP
total := total + a[index];
index := index + 1;
END LOOP;

RETURN total;
END;
' LANGUAGE 'plpgsql';

test=> select foo('{1,2}');
foo
-----
3
(1 row)
On Tue, 2003-12-16 at 03:25, K. Deepa wrote:

Hi all,
I am using postgresql7.4. How to handle arrays in plpgsql. How can
I pass an array. Is it possible to retrieve values from an array by
indexing it like

argument : '{1,2,3}'
Return value : varchar array

Variables :
---------

a alias for $1
b _varchar

Usage :
-----

b[1] = a[1];
b[2] = a[2];

return b;

Is it possible.

TIA,

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.