473,890 Members | 1,769 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #1
9 10947
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*******@postg resql.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(in vdet, total), YourFunction(in vdet, 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*******@postg resql.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*******@postg resql.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(in vdet, total), YourFunction(in vdet,
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*******@postg resql.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*******@postg resql.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*******@postg resql.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*******@postg resql.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%rowt ype;
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%rowt ype;
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 YourEmailAddres sHere" to ma*******@postg resql.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%rowt ype;
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*******@postg resql.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%rowt ype;
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_multiple 2()
RETURNS returntype
LANGUAGE plpgsql
AS '

DECLARE
myvar returntype%rowt ype;
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 YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 23 '05 #10

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

Similar topics

6
42199
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 instead of returning a recordset. CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 int OUTPUT) AS SELECT field2, field3 FROM Table WHERE field1 = @param1
1
8220
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. Here is the portion that does work: <working code> -- Function: public.get_factory_ytd() CREATE or replace FUNCTION public.get_factory_ytd() RETURNS setof record AS' declare ytd_record record; d_date record; begin for d_date in select distinct delivery_date from load_info_table order...
5
9408
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 and everything I could find in the mailing list I need to switch to 7.3 to get at least SETOF rows as a result. I can't really upgrade Postgres now. Is there is any a workaround idea to retrieve multiple rowsets?
22
3736
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 to how to do this (some said it was possible, and some said it wasn't but it was for older versions of Postgres). Basically I would *love* to be able to do something like this: SELECT some_id
3
22876
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 indexing it like argument : '{1,2,3}' Return value : varchar array Variables : ---------
3
5336
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 create_item_record_for_override(INTEGER, VARCHAR ) RETURNS RECORD AS ' DECLARE rec RECORD;
1
10972
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 field "id" Here's an example: CREATE OR REPLACE FUNCTION test()
5
14407
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 argument or arguments to the function, do some queries to return a set of records. I've done several functions that return one value of one type, but nothing that returns a set. -- Robert
0
3608
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 define my own TYPE and then return SETOF that type in the function. I’ve provided an example below. Now, if I have to add a column to the select query, I have drop the existing TYPE definition, create a new TYPE with the new column added to it, and then modify the function sql to return this...
0
11214
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10903
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10449
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9616
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8006
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7158
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5834
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6034
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4659
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.