Thread: Function RETURNS SETOF ???
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= $2OR 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 placeof <???????>. 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 'RETURNSSETOF' 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? thanks in advance, thalis
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
inplace 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
'RETURNSSETOF' 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