Re: Multiple return 'columns' from postgre pl/pgsql

From: Steve Tucknott <steve(at)retsol(dot)co(dot)uk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostGre <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Multiple return 'columns' from postgre pl/pgsql
Date: 2004-08-09 07:00:04
Message-ID: 1092034806.2555.10.camel@retsol1
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Tom,
Apologies about the name - I thought it was PostGre - SQL.

I did try a row type, but got an error.
Does the 'rowtype' have to exist as a definition in the database?

I get the following:
dev_vhr=# \i /tmp/stevet
psql:/tmp/stevet:67: ERROR: type "resultrec" does not exist

From the attached. If 'resultrec' has to exist, then we have quite a few
'definitions' we'd need to set up in the database.

Would returning a record type work - ie collate the information in the
function, then select all the variables into a record type from a dummy
table?
IE
..... get data into local variables, then .......

SELECT l_status, l_error_text,l_code,l_recNo,l_description
INTO myRec
FROM blankTable
WHERE recNo = 1;

Is that sensible? Does that also get away with just one local
definition?

On Sun, 2004-08-08 at 21:26, Tom Lane wrote:

Steve Tucknott <steve(at)retsol(dot)co(dot)uk> writes:
> Is there a way of doing:
> RETURNS INTEGER, CHAR(5), VARCHAR(200),.....
> IE return multiple values from a PL/PGSQL function?

You have to return a rowtype value.

There's an example in the 8.0devel docs:
http://developer.postgresql.org/docs/postgres/plpgsql-porting.html#PLPGSQL-PORTING-EX3

The example is making use of an 8.0-only feature (explicit names for
parameters) but otherwise I believe it would work in 7.4. Not sure
about pre-7.4.

BTW, the correct name of our software is PostgreSQL or informally
Postgres. No one associated with the project has ever called it
"Postgre". Pronounce it "post-gress" or "post-gress-cue-ell".

regards, tom lane

Regards,

Steve Tucknott

ReTSol Ltd

DDI: 01903 828769

Attachment Content-Type Size
stevet text/plain 1.8 KB

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Scott Marlowe 2004-08-09 08:49:25 Re: lock entire database
Previous Message Benjamin 2004-08-09 05:35:04 Re: lock entire database