472,351 Members | 1,446 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,351 software developers and data experts.

Returning multiple values (but one row) in plpgsql

I want to return multiple values, but not a set, only a single row,
from a
plpgsql function and I can't seem to get it to work. (I suppose I'd be
happy to return a set, but I can't seem to make that work either.
Anyway,
what's wrong with this?)

Version is:

$ rpm -q postgresql
postgresql-7.3.4-3.rhl9
$ cat /etc/redhat-release Red Hat Linux release 9 (Shrike)
Code is:

-- Tests for returning multiple values

CREATE TYPE returntype AS (a INT, b INT);

CREATE FUNCTION return_multiple()
RETURNS returntype
LANGUAGE plpgsql
AS '

DECLARE
myvar returntype;

BEGIN
myvar.a := 1;
myvar.b := 2;

RETURN myvar;
END;
';

SELECT return_multiple();

DROP FUNCTION return_multiple();
DROP TYPE returntype CASCADE;
Errors are:

WARNING: plpgsql: ERROR during compile of return_multiple near line 9
ERROR: return type mismatch in function returning tuple at or near
"myvar"

Thanks.

Karl <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #1
9 10628
Karl O. Pinc wrote:
I want to return multiple values, but not a set, only a single row,
from a
plpgsql function and I can't seem to get it to work. (I suppose I'd be
happy to return a set, but I can't seem to make that work either.
Anyway,
what's wrong with this?)

Version is:

$ rpm -q postgresql
postgresql-7.3.4-3.rhl9
$ cat /etc/redhat-release Red Hat Linux release 9 (Shrike)
Code is:

-- Tests for returning multiple values

CREATE TYPE returntype AS (a INT, b INT);

CREATE FUNCTION return_multiple()
RETURNS returntype
LANGUAGE plpgsql
AS '

DECLARE
myvar returntype;

BEGIN
myvar.a := 1;
myvar.b := 2;

RETURN myvar;
END;
';

SELECT return_multiple();

DROP FUNCTION return_multiple();
DROP TYPE returntype CASCADE;
Errors are:

WARNING: plpgsql: ERROR during compile of return_multiple near line 9
ERROR: return type mismatch in function returning tuple at or near
"myvar"

Thanks.

Karl <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

It works with 7.4.3, except the SELECT statement is
testdb=# SELECT * FROM return_multiple();
a | b
---+---
1 | 2
(1 row)

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

http://archives.postgresql.org

Nov 23 '05 #2
Hmmm,

Isn't it just easier to make a function which does that and add the
functions to the SELECT portion of statement instead of adding it to
the FROM clause?

as in:
Select invnum, YourFunction(invdet, total), YourFunction(invdet, othertotal)
FROM yourtable

Regards,
Arthur
On Wed, 8 Sep 2004 13:51:33 -0500, Karl O. Pinc <ko*@meme.com> wrote:
I want to return multiple values, but not a set, only a single row,
from a
plpgsql function and I can't seem to get it to work. (I suppose I'd be
happy to return a set, but I can't seem to make that work either.
Anyway,
what's wrong with this?)

Version is:

$ rpm -q postgresql
postgresql-7.3.4-3.rhl9
$ cat /etc/redhat-release Red Hat Linux release 9 (Shrike)

Code is:

-- Tests for returning multiple values

CREATE TYPE returntype AS (a INT, b INT);

CREATE FUNCTION return_multiple()
RETURNS returntype
LANGUAGE plpgsql
AS '

DECLARE
myvar returntype;

BEGIN
myvar.a := 1;
myvar.b := 2;

RETURN myvar;
END;
';

SELECT return_multiple();

DROP FUNCTION return_multiple();
DROP TYPE returntype CASCADE;

Errors are:

WARNING: plpgsql: ERROR during compile of return_multiple near line 9
ERROR: return type mismatch in function returning tuple at or near
"myvar"

Thanks.

Karl <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3

On 2004.09.08 14:25 Arthur Hoogervorst wrote:
Hmmm,

Isn't it just easier to make a function which does that and add the
functions to the SELECT portion of statement instead of adding it to
the FROM clause?

as in:
Select invnum, YourFunction(invdet, total), YourFunction(invdet,
othertotal)
FROM yourtable
My function is both computationaly intensive and has side effects,
necessitating a single function call.


Regards,
Arthur
On Wed, 8 Sep 2004 13:51:33 -0500, Karl O. Pinc <ko*@meme.com> wrote:
I want to return multiple values, but not a set, only a single row,
from a
plpgsql function and I can't seem to get it to work. (I suppose I'd

be
happy to return a set, but I can't seem to make that work either.
Anyway,
what's wrong with this?)

Version is:

$ rpm -q postgresql
postgresql-7.3.4-3.rhl9
$ cat /etc/redhat-release Red Hat Linux release 9 (Shrike)

Code is:

-- Tests for returning multiple values

CREATE TYPE returntype AS (a INT, b INT);

CREATE FUNCTION return_multiple()
RETURNS returntype
LANGUAGE plpgsql
AS '

DECLARE
myvar returntype;

BEGIN
myvar.a := 1;
myvar.b := 2;

RETURN myvar;
END;
';

SELECT return_multiple();

DROP FUNCTION return_multiple();
DROP TYPE returntype CASCADE;

Errors are:

WARNING: plpgsql: ERROR during compile of return_multiple near line

9
ERROR: return type mismatch in function returning tuple at or near
"myvar"

Thanks.

Karl <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of

broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that

your
message can get through to the mailing list cleanly


---------------------------(end of
broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that
your
message can get through to the mailing list cleanly


Karl <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #4
Poo. Looks like my postgresql has a bug. :-(

The only work-around I can think of is to return
a bunch of values in a delimited string and then
parse them out upon return. :-(

Anybody got a better idea? Anybody know just when
this was fixed? If I knew I might be able to see about
getting our site upgraded. (I tried looking in the CVS
web interface, but was quickly daunted by the number of
source files.)

On 2004.09.08 14:07 Ron St-Pierre wrote:
Karl O. Pinc wrote:
I want to return multiple values, but not a set, only a single row,
from a
plpgsql function and I can't seem to get it to work. (I suppose I'd
be
happy to return a set, but I can't seem to make that work either.
Anyway,
what's wrong with this?)

Version is:

$ rpm -q postgresql
postgresql-7.3.4-3.rhl9
$ cat /etc/redhat-release Red Hat Linux release 9 (Shrike)
Code is:

-- Tests for returning multiple values

CREATE TYPE returntype AS (a INT, b INT);

CREATE FUNCTION return_multiple()
RETURNS returntype
LANGUAGE plpgsql
AS '

DECLARE
myvar returntype;

BEGIN
myvar.a := 1;
myvar.b := 2;

RETURN myvar;
END;
';

SELECT return_multiple();

DROP FUNCTION return_multiple();
DROP TYPE returntype CASCADE;
Errors are:

WARNING: plpgsql: ERROR during compile of return_multiple near line
9
ERROR: return type mismatch in function returning tuple at or near
"myvar"

Thanks.

Karl <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of
broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that
your
message can get through to the mailing list cleanly

It works with 7.4.3, except the SELECT statement is
testdb=# SELECT * FROM return_multiple();
a | b
---+---
1 | 2
(1 row)

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

http://archives.postgresql.org


Karl <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

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

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

Nov 23 '05 #5
Karl O. Pinc wrote:
Anybody got a better idea? Anybody know just when
this was fixed? If I knew I might be able to see about
getting our site upgraded. (I tried looking in the CVS
web interface, but was quickly daunted by the number of
source files.)


select version();
version
-------------------------------------------------------------------------
PostgreSQL 7.3.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7)
(1 row)

CREATE TYPE returntype AS (a INT, b INT);
CREATE OR REPLACE FUNCTION return_multiple()
RETURNS setof returntype
LANGUAGE plpgsql
AS '
DECLARE
myvar returntype%rowtype;
BEGIN
myvar.a := 1;
myvar.b := 2;
RETURN NEXT myvar;
RETURN;
END;
';
SELECT * FROM return_multiple();
a | b
---+---
1 | 2
(1 row)

HTH,

Joe

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #6
Thanks! I was not declaring the variable %rowtype.
Adding that fixed the problem.

On 2004.09.08 15:46 Joe Conway wrote:
Karl O. Pinc wrote:
Anybody got a better idea? Anybody know just when
this was fixed? If I knew I might be able to see about
getting our site upgraded. (I tried looking in the CVS
web interface, but was quickly daunted by the number of
source files.)


select version();
version
-------------------------------------------------------------------------
PostgreSQL 7.3.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7)
(1 row)

CREATE TYPE returntype AS (a INT, b INT);
CREATE OR REPLACE FUNCTION return_multiple()
RETURNS setof returntype
LANGUAGE plpgsql
AS '
DECLARE
myvar returntype%rowtype;
BEGIN
myvar.a := 1;
myvar.b := 2;
RETURN NEXT myvar;
RETURN;
END;
';
SELECT * FROM return_multiple();
a | b
---+---
1 | 2
(1 row)

HTH,

Joe


Karl <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #7
Karl O. Pinc wrote:
Thanks! I was not declaring the variable %rowtype.
Adding that fixed the problem.


Ah yes, that works too. For the record:

CREATE TYPE returntype AS (a INT, b INT);
CREATE OR REPLACE FUNCTION return_multiple()
RETURNS returntype
LANGUAGE plpgsql
AS '
DECLARE
myvar returntype%rowtype;
BEGIN
myvar.a := 1;
myvar.b := 2;
RETURN myvar;
END;
';
SELECT * FROM return_multiple();
a | b
---+---
1 | 2
(1 row)

Joe

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #8

"Karl O. Pinc" <ko*@meme.com> writes:
Anybody got a better idea?


If they're all the same data type you could alternatively use an array. Which
is more convenient might depend on how much you want to throw around the
composite data type in intermediate code before peeking at the elements.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #9
Once I've gotten multiple values back from a plpgsql function,
how do I actually reference those values in another plpgsql
function? I've tried several syntaxes and keep getting errors.

Various attempts are below.

Thanks.

On 2004.09.08 15:59 Joe Conway wrote:

Ah yes, that works too. For the record:

CREATE TYPE returntype AS (a INT, b INT);
CREATE OR REPLACE FUNCTION return_multiple()
RETURNS returntype
LANGUAGE plpgsql
AS '
DECLARE
myvar returntype%rowtype;
BEGIN
myvar.a := 1;
myvar.b := 2;
RETURN myvar;
END;
';
SELECT * FROM return_multiple();
a | b
---+---
1 | 2
(1 row)


PostgreSQL 7.3.4 on i386-redhat-linux-gnu, compiled by GCC
i386-redhat-linux-gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5)
CREATE FUNCTION return_multiple2()
RETURNS returntype
LANGUAGE plpgsql
AS '

DECLARE
myvar returntype%rowtype;
a INT;
b INT;

BEGIN

-- SELECT INTO a, b FROM return_multiple();
SELECT INTO a, b return_multiple();
myvar.a := a;
myvar.b := b;

-- SELECT INTO myvar return_multiple();

RETURN myvar;
END;
';
Karl <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Samuel Hon | last post by:
Hi I'm not sure what the best approach for this is: I have a stored procedure which I would like to use to return several output values...
1
by: Julie May | last post by:
I have 90% of my function working and I know to get the next 10% it is justa matter of getting the quotations and the escaping quotations correct....
5
by: Oksana Yasynska | last post by:
Hi all, I'm running Postgres 7.2.1 and I need to return multiple row sets from plpgsql function. I'm new in plpgsql but according documentation...
22
by: Christopher Murtagh | last post by:
Greetings, I'm trying to write a pl/perl function that will return multiple rows. I've looked all over the web and only found vague references as...
3
by: K. Deepa | last post by:
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...
3
by: ezra epstein | last post by:
I'm been banging my head over this for a little while now. Here's a simple function to return a record: <code> CREATE OR REPLACE FUNCTION...
1
by: Pascal Polleunus | last post by:
Hi, I'm trying to return a RECORD from a function, but when I try to use the variable I have the following error: ERROR: record "r" has no...
5
by: Robert Fitzpatrick | last post by:
Can someone point me to some more information or perhaps show an example of returning a recordset from a plpgsql function. I'd like to send an...
0
by: lazybee26 | last post by:
Hello – I’m trying to findout a better solution to this approach. Currently if I have to return columns from multiple tables, I have to...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
1
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.