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

Is there a way of doing:
RETURNS INTEGER, CHAR(5), VARCHAR(200),.....
IE return multiple values from a PL/PGSQL function?

(From what I can see so far, you only have 1 return field - although that can be a rowtype or record)

I am converting Informix functions, and returning multiple columns was allowed -  I need to be able to do the same/similar in PostGre. What's the best way?

(Typically the functions return an error code, error text (that are not table based - ie are built in the function and not selected from a table) and two or three columns from a table).

Regards, Steve Tucknott ReTSol Ltd DDI: 01903 828769

Steve Tucknott <steve@retsol.co.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

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@retsol.co.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

Steve Tucknott <steve@retsol.co.uk> writes:
> Does the 'rowtype' have to exist as a definition in the database?

In the form I showed, yes.

> Would returning a record type work -

Only if you're prepared to specify the actual record type in the calling
query.  The point is that in

    select * from myfunc(...);

the parser has to have some way of understanding what * expands to,
and it needs the info in advance of calling the function.  So you
either need to return a named rowtype, or return record and specify
what you're expecting in the call.  From memory it's something like

    select * from myfunc(...) AS (f1 int, f2 text, ...);

but see the docs.  In practice I think the named rowtype is easier in
99% of cases.  The returns-record case is really meant for functions
that can actually return different rowtypes depending on the parameters
they are given, like dblink() does.  If you're thinking of doing something
like that, you probably shouldn't be asking about it on the novice list ;-)

            regards, tom lane

Tom,
Thanks again for the reply.

We're calling the 'function' from a 4gl program - I'm just trying the 'Record' type route to make sure that the 4gl understands the returned type. If not, then named row types will have to be the option.

Cheers again.

On Mon, 2004-08-09 at 15:23, Tom Lane wrote:

Steve Tucknott <steve@retsol.co.uk> writes:
> Does the 'rowtype' have to exist as a definition in the database?

In the form I showed, yes.

> Would returning a record type work -

Only if you're prepared to specify the actual record type in the calling
query.  The point is that in

	select * from myfunc(...);

the parser has to have some way of understanding what * expands to,
and it needs the info in advance of calling the function.  So you
either need to return a named rowtype, or return record and specify
what you're expecting in the call.  From memory it's something like

	select * from myfunc(...) AS (f1 int, f2 text, ...);

but see the docs.  In practice I think the named rowtype is easier in
99% of cases.  The returns-record case is really meant for functions
that can actually return different rowtypes depending on the parameters
they are given, like dblink() does.  If you're thinking of doing something
like that, you probably shouldn't be asking about it on the novice list ;-)

			regards, tom lane

---------------------------(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

Regards, Steve Tucknott ReTSol Ltd DDI: 01903 828769