Function RETURNS SETOF ???

Hi,

Tell me please, how to alter tables with arrays
(in other words - to change the dimension of array )
I'm running PG 7.1.2

Thanks for any suggestions .

Igor

Igor <dbmanager@osb368.nnov.ru> writes:

Tell me please, how to alter tables with arrays
(in other words - to change the dimension of array )

Just assign a new array value to the column. PG doesn't actually
consider the dimensionality of an array to be part of the type
specification; any size array will be accepted.

regards, tom lane

Thalis A. Kalfigopoulos wrote:

Helloppl,

I have a long query that (summerized) looks something like:

SELECT A.a,B.b
FROM A,B
WHERE A.x=B.x AND (A.y=const1 OR A.y=const2 OR A.y=const3);

where the user provides const1,2,3 at runtime. The problem is in creating a function out of it:
CREATE FUNCTION myfunc(int4,int4,int4) RETURNS <???????> AS 'SELECT A.a,B.b FROM A,B WHERE A.x=B.x AND (A.y= $1 OR A.y= $2 OR A.y= $3)' LANGUAGE 'sql';

The question is what do I set as the return value? I'm returning a SETOF something but not something specific to put in place of <???????>. I tried 'RETURNS SETOF (int4,int4)' but didn't work.

One workaround I figured was to create a new view that would give the schema definition I need to reference in my 'RETURNS SETOF' clause. This succeeds in creating the function, but the function doesn't work. I do a select myfunc(1,2,3) and I get something like:

?column?
-----------
136361584
136361584
136361584
(3 rows)

At first I thought it was oids, but the aren't.
Any ideas what this result is and how I can make this thing work?

The result is the memory address(es) of the heap tuples used
in the executor. Not that useful.

In the v7.2 development tree we currently have this:

CREATE TABLE A (x integer, y integer, a text);
CREATE TABLE B (x integer, b text);

INSERT INTO A VALUES (1, 11, 'one from A');
INSERT INTO A VALUES (2, 22, 'two from A');
INSERT INTO A VALUES (3, 33, 'three from A');

INSERT INTO B VALUES (1, 'one from B');
INSERT INTO B VALUES (2, 'two from B');
INSERT INTO B VALUES (3, 'three from B');

CREATE FUNCTION myfunc (refcursor, integer, integer, integer)
RETURNS refcursor AS '
DECLARE
curs ALIAS FOR $1;
y1 ALIAS FOR $2;
y2 ALIAS FOR $3;
y3 ALIAS FOR $4;
BEGIN
OPEN curs FOR SELECT A.a, B.b
FROM A, B
WHERE A.x = B.x AND
(A.y = y1 OR A.y = y2 OR A.y = y3);
RETURN curs;
END;'
LANGUAGE 'plpgsql';

BEGIN;
SELECT myfunc('c1', 11, 22, 44);
myfunc
--------
c1
(1 row)

FETCH ALL IN c1;
a | b
------------+------------
one from A | one from B
two from A | two from B
(2 rows)

CLOSE c1;
COMMIT;

BEGIN;
SELECT myfunc('c1', 33, 44, 55);
myfunc
--------
c1
(1 row)

FETCH ALL IN c1;
a | b
--------------+--------------
three from A | three from B
(1 row)

CLOSE c1;
COMMIT;

BEGIN;
SELECT myfunc('c1', 0, 0, 0);
myfunc
--------
c1
(1 row)

FETCH ALL IN c1;
a | b
---+---
(0 rows)

CLOSE c1;
COMMIT;

I don't know when v7.2 will happen, but I think it shouldn't
take as long as v7.1 did.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Hello,

Thank you for replay , but i still having problems with aray..

Well, i have a field OSTV which has dimention [1:9][1:6]
(i can see it in "SELECT array_dims(ostv) FROM mytable")

when i'm trying:
"update mytable set ostv [10][3]=333.00"
i got:
"Error while executing the query (non-fatal);
ERROR: Invalid array subscripts"

if i make empty field wiht the neccessary dimension [10][6]
i couldn't move data from old field , because after

"update mytable set ostvNewDim = ostvOldDim"

sets new dimension to old value

Tell me please, what to do?

TL> Just assign a new array value to the column. PG doesn't actually
TL> consider the dimensionality of an array to be part of the type
TL> specification; any size array will be accepted.

Igor <dbmanager@osb368.nnov.ru> writes:

Well, i have a field OSTV which has dimention [1:9][1:6]
when i'm trying:
"update mytable set ostv [10][3]=333.00"

You can't extend an array unless you specify values for all the new
entries required to fill a rectangular array value (which in practice
means only one-dimensional arrays can be extended this way).

I'd like to see the array code generalized to allow you to do that,
inserting NULLs for the missing entries. But it'd take some work,
and so far no one's stepped up to the plate to do the work. Would
you like to volunteer?

regards, tom lane